Clients often think that I know more Excel than I do, because I know how to use some functions such as IF, CONCAT and more, but my knowledge of Excel is quite limited when it comes to charts, graphs and PivotTables. I was recently writing a report for a client using Jet Reports to pull Intrastat information from Microsoft Dynamics 365 Business Central (both Jet and Business Central are irrelevant as far as the detail fo this post are concerned).
One of the things I wanted to do was create a summary of the data pulled from Business Central, which meant using a standard Excel PivotTable, something with which I have almost no experience. I managed to get most of the PivotTable configured without issue, but I needed to perform a calculation within the PivotTable and it took me a while to work out.
This is done by adding a calculated field through the PivotTable Analyze tab on the action bar; click the Fields, Items & Sets » Calculated Field button:
Set the Name field to the name of the field you want; then you need to construct the required calculation in the Formula field:
Leave the = at the start of the Formula field and remove the 0; in the Fields list scroll down till you find the fields needed and click Insert Field; mathematical symbols you can just type. when the formula is complete, click OK and the new field will be available in the PivotTable side pane.
What should we write about next?
If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.
