Computing grand totals of Discrete Measures using RUNNING SUM and LOOKUP filter in Tableau
Task: Create a detail report in Tableau and keep multiple discrete measures between 2 discrete dimensions.
As we all know, continuous measures can’t be kept between 2 discrete fields, thus it is required to make continuous measures as discrete.
After doing this, now if we want to display Grand Totals for these Discrete Measures using Analysis->Totals->Show Column Grand Totals in Tableau, then tableau returns NULL for grand totals of Discrete measures as seen in below screenshot.
To handle this NULL issue, follow the below steps to compute grand totals using Running SUM and Lookup table calculation filter:
- Duplicate the existing report and rename worksheet as “Grand Totals”.
- Apply the table calculation “Running Total” on all discrete measures (Sales, Profit, Discount)
3. Go to “Right Click on each measure pill → Edit table calculation → Specific Dimension →Select “Market” on which Running Total will do computation.
4. After applying “Running Sum” to all discrete measures, Grand Total worksheet will look like below:
At this step, you are getting “Grand Total” for each discrete measure for last record in the view which is Index=7 and Market=‘US’.
5. Create a Parameter Top N as below:
6. Create a lookup filter with following condition and drag it to filters shelf. Compute the filter using “Table Down” and set it to “True”.
Here, Lookup offset is set to 0 which means return the Index row at 0th Offset, depending selected Top N value.
For example, If Top N=5 then Grand Totals is computed considering only first 5 rows and lookup filter will return 5th Row having grand totals which is currently at 0th Offset.
If Top N=7 then Grand Totals is computed considering only first 7 rows and lookup filter will return 7th Row having grand totals which is currently at 0th Offset.
We are able to achieve this using LOOKUP function because Table calculation filters in Tableau gets executed in the last and computes all the calculation defined in report prior execution of LOOKUP filter. Thus, here Tableau is computing Running Total of measures first and Lookup is returning Top Nth row having grand totals.
7. Create a new filter “Index#” with below logic for “Detail Records” worksheet to show only Top N records and drag it to filter shelf computing with “Table Down”
8. Hide unnecessary headers from “Grand Totals” worksheet such as INDEX() and Market etc so that we can drag only grand totals on dashboard below main report.
9. Create a new dashboard, add Vertical Layout, drag “Detail Records” worksheet first and “Grand Totals” worksheet below it. See below how it should look.
