Calculating percentages by a dimension and displaying cumulative percentages

Use-case

I have two dimensions I’m aggregating across in a cross-tab - product_category and region. I would like to display the percentage of sales with each product_category by region. Furthermore, I’d also like to see the cumulative percentage of sales in ascending order across my different regions.

Solution

Step 1: Use an Analytic (Window) function to calculate the percentage of sales for each region within each product category:

Setting up the Analytic Function:

Actual Calculation from Analytic (Window) Function:

End result:

Step 2: Save your new Analytic (Window) function (percent(sales)) as a Derived Data column subquery within your Visual. NOTE: Its helpful when creating Derived Data columns to give them a new and unique name so not to confuse with your original calculation.

Step 3: Add your Derived Data column to the Measures shelf and modify the calculation to use a second Analytic (Window) function that will perform a cumulative sum across each of your consecutive percentages.

40%20PM

Actual Calculation:

NOTE: Using the avg() function around the Derived Data column allows you to maintain the average percentage of sales calculation by region, and the sum() wrapper around the outside indicates that we want to add up our percentages of sales as we start to move from region to region downward on the crosstab.

End result: