Skip to main content
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

New Member

Calculating Measure Total using one measure In Matrix Drilldown



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:



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.




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?
Super User
Super User

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

then you can try

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

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.





Helpful resources

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.


Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors