Problem: I want to show total sales by multiple dimensions (i.e. region and shipping mode). However, I only want to display the top 3 region by total sales.
This isn’t possible if I use the Top K option since that will only show the top 3 total sales by region-shipping mode combinations.
Solution: This requires multi-pass SQL to achieve the desired result. First we must use an Analytic (Window) function to construct a rank based on total sales, save our rank function as a Derived Data field in our Visual, and then use our rank function to filter our visual to only show the Top 3 regions based on rank of total sales.
Step 1: Construct your subquery (Derived Data column) that will calculate and rank your regions by total sales.
The best way to get started is to use a table visual to create the data structure that be will used to reference your regions ranked by total sales.
To apply a RANK() function to your total sales, add sales again to the Measure shelf, click on the element, select the “Analytic Functions” dropdown and then select “Rank”:
In this case we want to rank sales across the entire result set without any partitioning (entities), and we also want to rank in ascending order by total sales, which means we can leave the basic configurations alone.
If you explore the code behind the rank function, you should see something like this:
After applying the rank function, you should now see total sales by region ranked in ascending order.
Step 2: Save your subquery expression as a Derived Data column
If you click on the “rank” element in your Measure shelf, and then click “Save Expression” you’ll be given an option to save this subquery to the Visual builder. Give your rank function a unique name to ensure there’s no reference name overlaps in your Dataset (i.e. total_sales_rank)
Now you should be able to see your Derived Data column in green on just below the Dimension shelf title:
Step 3: Clear your shelves, add your original dimensions (region, ship_code), your measure (sales), and filter by your Derived Data “rank” column.
Since your previous shelf elements were used to construct your subquery, its best to remove everything and give yourself a clean slate before we move onto your main query:
Once you clear your shelves, add back your dimensions and measures:
And finally add your “total_sales_rank” Derived Data column to filter shelf and create a custom expression to filter down to the top 3 ranked regions:
Now you should only see 3 regions in your query:
Step 4: Update your visual to use the original stacked bar chart
Now that you’ve got the query constructed properly, we can display the dimensions and measures like we originally intended through a Stacked Bar chart.