Forum Discussion
Subtotal bar chart with measure
Hi,
I created a dashboard with built-in dynamic switch to show the top and bottom 5, 10, and 15 performers by vendors and region (similar to this dashboard).
Based on the dashboard link, Total revenue was $4B. The dashboard displays the top 5 products by revenue when selected, but mgt wants to see the total top 5 and % of the total (e.g. total products revenue were $4B. Top 5 product sales is $0.58B. Top 5 % of Total iss 14.5%).
Is there a measure that can summarize only the top/bottom products that are being displayed?
Link to download working dashboard with dynamic filters.
2 Replies
- MattAllington
Community Champion
What you need to do is write a new measure that returns the total revenue without the top 5 filter applied. From there it is a simple division.
So you have a top 5 list of something. You need to remove the filters on that “something”
The general pattern to do this is a measure something like this
CALCULATE([Total Revenue], ALL(Table))
where table is the one that filters your original visual by rows. So if you are showing top products, the use the products table.
v-jiascu-msft I'm not sure what happened with the other post. I've uploaded the PBIX file in the original post in Dropbox. You can download the file by clicking on the link.
Thank you.
MattAllington I tried your suggestion, but only got the total, not subtotal of what's being shown on the screen.
Measure = CALCULATE ('Measure' [Measure Selection], 'Top'[Select View])
'Measure' is the switch statement for total savings and total spend
'Top'[Select View] is the filter for 5, 10, 15
