Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
qthuynh
Frequent Visitor

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.

 

sum.JPG

 

 

2 REPLIES 2
qthuynh
Frequent Visitor

@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

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. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.