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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Matrix group by code values and subtotal on those groups

Hello,

Need a little assistance here and thanks in advance.

 

I have the following PBI report. And the main focus is program code, I need to group by program code and then subtotal by the program code groups. But due to the detail level of the report, it sums program code for the very lowest detail level in the report.

 

The second screenshot is what I am trying to achive. 

Thanks

Tom

PBI Subtotal testPBI Subtotal test

 

 

Desired outputDesired output

1 ACCEPTED SOLUTION
AilleryO
Memorable Member
Memorable Member

Hi,

 

You need to add an ALL or REMOVEFILTERS functions. They will help you to get rid of the filter "created" by your visual. Each line is a "filter" in your table.

So to get the desied level of calculation, you need to remove those filters.

If you add a CALCULATE (SUM(XXX), ALL(name of columns not to be considered as filters)

or

CALCULATE (SUM(XXX), REMOVEFILTERS(name of columns not to be considered as filters)

 

Let us know

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Hello, I was able to use the dax and create the measure column. So thank you.

See image. It does end up having a column (Prog Total) with that data valuesTestingTesting repeating.

 

I really want the format to be the same as the excel example, is this feasible do you know of a way to make the matrix look like the spreadsheet example? Or should I create another post for the format of the matrix? I have been toying around with it and just cannot have it appear the way ths spreadsheet does. Thanks again.

Hi,

 

You'll not get something exactly as it is in Excel, but if you use the visual called Matrix (not Table), you should be able to get something quite close 😉

same, same but different 😉

The idea of a new topic concerning only the display/formatting, sounds good to me as well.

Have a nice day,

 

AilleryO
Memorable Member
Memorable Member

Hi,

 

You need to add an ALL or REMOVEFILTERS functions. They will help you to get rid of the filter "created" by your visual. Each line is a "filter" in your table.

So to get the desied level of calculation, you need to remove those filters.

If you add a CALCULATE (SUM(XXX), ALL(name of columns not to be considered as filters)

or

CALCULATE (SUM(XXX), REMOVEFILTERS(name of columns not to be considered as filters)

 

Let us know

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.