In the data school training session last week, I introduced how to calculate the percent of total using the FIXED level of detail expression. In this article, I summarise four different ways to achieve this. Two from table calculation, another two are from LOD. I’m using superstore data as an example in this article; for example, our view level of detail is sales of each sub-category; please find out the percent of total sales of overall sales. The final view is like the below image; as you can see, four different methods show the same outcome, but once you add the filter on that, this will change the result in some terms; you will find further information about this below.
so the four different methods are:
1. Total(expression)
Syntax: SUM(Sales)/TOTAL(SUM(Sales))
The Total(expression) returns the total for the given expression; in this example, it is the sum of sales. The method is used in the quick table calculation by default; once you change the quick table calculation to the total percentage, you can double-click on that pill that checks the calculation. The detail shows as below.
2. Window Sum
Syntax: SUM([Sales])/WINDOW_SUM(SUM([Sales]))
Window sum returns the sum of the expression within the window. If you don’t add the start & end index after the sum of sales, it will calculate every row of the dataset.
3. Exclude LOD
Syntax: SUM(Sales)/SUM(Exclude [Sub-Category]: SUM(Sales)}
Exclude LOD means you take the view level out in the calculation, calculate on the lower granular level; in this case, the view level of detail is sales of each sub-category, so in the calculation, you have to exclude sub-category.
4. FIXED LOD
Syntax: SUM(SALES)/{FIXED: SUM(SALES)}
Using this method is because of the filter; any filters will not affect the context filter result. But it will change for the other three methods. In some scenarios, you might want to filter to one sub-category only; however, you still want to see the percent of the total of overall sales, including other sub-categories. This is the perfect use case. Another thing you might concern about is there is no dimension in the LOD expression, which means the sum of every sale that totally aggregated.
If I exclude chairs from the view, you can see that percentage will change for the other three methods but not affected by the Fixed LOD calculation.
If I want to affect the Fixed LOD method? The way you can do it is to change the filter to context filter; the below image shows you the order of operations. The context filter takes priority over any LOD expressions or table calculations.
I hope this article is helpful. If you have any enquiry regarding this matter, please feel free to contact me.
