on - edited on by
DRay
Analytical Need
Calculate the pivot grand total at the row level to do calculations such as % of the grand total. You may have tried the following and not received the desired results:
This will ignore your row groupings, which is what we need – however this will also ignore your dashboard filters on those fields, which is probably not the desired result.
This may work in some scenarios, such as if you only have one row dimension. But it may not work with more dimensions, depending on your data. Let’s look at a simple example:
We have one table with the following data:
Here are the calculations and the Pivot result, which shows the amount, the multi-pass calculation to get each category total, and a nested multi-pass to calculate the grand total at the row level.
Amount = sum([Amount])
Category Amount = SUM([Brand],[Total Amount])
Calculated Grand Total = SUM([Category], MAX([Category Amount]) )
The category amounts are correct, but the calculated grand total incorrectly shows 1820 instead of 2715 on some rows. This occurs because we do not have any rows for Category B – Brand Z, so the multi-pass only sums up the categories for which Brand Z exists (680 + 1140 = 1820).
Solution
There are multiple solutions and the best option will depend on your use case.
Option 1: Add in another Multi-Pass
Depending on the number of dimensions you have, you can try nesting another multi-pass. Here I have:
MAX([Brand],MAX(SUM([Category],MAX(SUM([Brand],[Total Amount])))))
This will return 2715 in every row.
Option 2: Add rows with zero amounts to your data model for missing combinations of your dimensions
This option may only be viable if you have a relatively small data set or if there are few missing combinations. Additionally, adding rows with zero values could have an impact on certain calculations such as AVG.
Once this missing rows are added, we’ll see it in the pivot and our calculated grand total shows the correct amount for every row:
Option 3: Duplicate columns in your model and use ALL function
This method requires duplicating the fields you need in your data model. It could be a good option for Internal Analytics, when the dashboard designers will know exactly what to do with this duplicated fields, but may not be intuitive to a large dashboard designer user base.
Follow these steps:
- In your model, create duplicates of the fields you will use as rows in your pivot. The resulting table looks like this
- Use one set of columns for your dashboard filters, and the other set in the pivot. Here let’s assume that the original Category and Brand are being used as dashboard filters, so add the duplicate set as the rows in your pivot (you can change the label to the real name):
- The formula to calculate the grand total should also reference this duplicate set and should now be
( [Total Amount] , ALL([Brand2]) , ALL([Category2]))
We get the correct results in the pivot because we are ignoring our groupings but still respecting dashboard filters:
*When using this technique, make sure that the widget option Widget affects dashboard filters is turned off for this pivot - otherwise if a user select one of your duplicate dimension fields, it will add it as a dashboard filter.
