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
ReedSavory
Regular Visitor

Is it possible to use autocalculated subtotals in Matrix views to perform calculations?

When using a PowerBi Matrix view, is it possible to use the automatically calculated subtotals in other calculations, and if so, what are these called and how would someone use them in calculations for other columns or "values"?

I've created a sample matrix here, and circled in red the subtotal fields I'm referring to, both the "name" and "subtotal" values.

Any thoughts?

ReedSavory_0-1717185697676.png

 

1 ACCEPTED SOLUTION

Hi @ReedSavory 

 

You can try with ALLEXCEPT(). 

Uzi2019_0-1717669216144.png

 

Put all the value where you want to keep the filter value also.see i want category wise number so i have put categry under ALLExcept (). you can put multiple fileds in ALLExcept.

 

I hope i answered your question!

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

View solution in original post

6 REPLIES 6
Uzi2019
Super User
Super User

Hi @ReedSavory 

You can create measure like below
Measure1= Calculate( sum( Balance), Allselected([program name]))

Uzi2019_0-1717563269192.png

 

 

I hope I answered your question!

 





Don't forget to give thumbs up and accept this as a solution if it helped you!!!

It's definitley closer than I've been getting, but doesn't give the right results because I have nearly a half-dozen filters on the page to ensure I get only the dataset needed.  Without those filters I get too much data so the calculations are incorrect.  Can you suggest a way to use that method but also keep all my filers (I assume there's some way to include all those filter functions as part of the dax?)

Thanks again.

Hi @ReedSavory 

 

You can try with ALLEXCEPT(). 

Uzi2019_0-1717669216144.png

 

Put all the value where you want to keep the filter value also.see i want category wise number so i have put categry under ALLExcept (). you can put multiple fileds in ALLExcept.

 

I hope i answered your question!

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Stay away from ALLSELECTED. Use REMOVEFILTERS on specific columns instead.

I've tried your method and while I'm getting a narrower data set, I'm still getting the bottom line total every time (the TOTAL at the bottom of the page), not simply the total for that group, and again all the data I'm trying to filter-out shows up in the view now.  The first column is the subtotal that PowerBI is automatically creating in the matrix, the last column SHOULD match that value as a calculation if this was working, and instead every single cell simply contains the totals of the entire sheet.

ReedSavory_0-1717606244428.png

It seems like I have to be able to add the filters into the calculate statement, somehow.



lbendlin
Super User
Super User

Create measures that use REMOVEFILTERS  on the Program Name field.

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.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.