In this episode of Tableau 360, we’ll look at four ways to calculate percent-of-total. There are two table calculations and two level-of-detail calculations.
Here are the four ways:
Table Calculation #1: TOTAL()
// Percent of Total - TOTAL()
SUM([Measure])/TOTAL(SUM([Measure]))
Pros: Works for any set of measures and dimensions for that measure.
Cons: You have to configure your Table Calculation every time you add it to a visualization.
Table Calculation #2: WINDOW_SUM()
// Percent of Total - TOTAL()
SUM([Measure])/WINDOW_SUM(SUM([Measure]))
Pros: Works for any set of measures and dimensions for that measure. Effectively works the same as TOTAL().
Cons: You have to configure your Table Calculation every time you add it to a visualization.
Level-of-Detail #1: {FIXED}
// Percent of Total - TOTAL()
SUM([Measure])/SUM({FIXED [Dimension]: SUM([Measure])})
Pros: Will create any percent-of-total calculation for the [Dimension]Â specified in the denominator. If you are looking for percent-of-total based the overall total in your data set you can use {SUM([Measure])}.
Cons: The denominator will only ever be the dimension specified in the denominator.
Level-of-Detail #2: {EXCLUDE}
// Percent of Total - TOTAL()
SUM([Measure])/SUM({EXCLUDE [Dimension]: SUM([Measure])})
Pros: Will create a percent-of-total calculation across any set of dimensions for the [Dimension] on the view.
Cons: The numerator will only ever be the the dimension specified in the denominator and this dimension must be on the view in the visualization.
