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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

Calculating Measure Total using one measure In Matrix Drilldown

Hi,

 

I found an amazing post @Greg_Deckler solving the issue of measure total allows the "Total" to display the sum of the values in the column.

 

Here is the link to his post: Measure Totals, The Final Word - Microsoft Power BI Community

 

But it seems he also suggests you have to create separate measures for different columns by saying: "Is it annoying to have to create multiple measures and specifically tailor them to each individual visual? Yes, yes it is."

 

Now, I am having trouble calculating the measure total at different levels of matrix drilldown using one measure. Here is my example.

 

This is the data set I used:

QishengYin7_1-1619621950686.png

 

This is the single measure:

Single Measure = sum(Table1[Value])-50

 

These are the measures to calculate measure total for Catory1 and Category2:

 

Measure Total Category 1 = sumx(values(Table1[Category1]), [Single Measure])
 
Measure Total Category 2 = sumx(values(Table1[Category2]), [Single Measure])
 
As you can see, Measure Total Category 1 only shows total at Category 1 level of drilldown and Measure Total Category 2 only shows total at Category 2 level of drilldown.
 
QishengYin7_3-1619622218579.png

 

 QishengYin7_4-1619622234204.png

 

 
 
Is there a way to create one measure showing category 1 total(3200) at category 1 drilldown and category 2 total(3500) at category 2 drilldown?
 
Regards,
Qisheng 
2 REPLIES 2
amitchandak
Super User
Super User

@Anonymous , do you want to subtract 50 in each line?

then you can try

Single Measure = sumX(Table1,Table1[Value]-50)

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

Unfortunately, it doesn't work.

 

I named the measure you suggested "Solution". However, that measure only calculates the Single Measure at the lowest level in the table.

 

When you at the Category1 level, which is the "smallest", it has the result I expect. But it stays the same when I switched to Category2 Level.

 

QishengYin7_0-1619629600205.png

QishengYin7_2-1619629667264.png

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!