cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rdaudt2021
Frequent Visitor

Matrix - Subtotal across columns

Hi.

 

I am using the Matrix visual and all looks good. There is one thing that I can't find a way to do, though: to show a subtotal across  the columns, as indicated in red in the attached image. I tried every format configuration but this subtotal wont show up. Do you know if this possible?

 

Please, note that I am using the Power BI Desktop from May 2020, which is optimized for deployment to Power BI Server (on-prem).

 

I appreciate anyhelp with this.

 

Thanks

 

 

 

IMG_5297 (1).JPG

1 ACCEPTED SOLUTION

Hi  @rdaudt2021 ,

 

Is your issue solved now?

If you cant get what you need,would you pls provide more sample data with expected output?

 

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

 

View solution in original post

6 REPLIES 6
rdaudt2021
Frequent Visitor

Hi.

 

I am still in the process of implementing the solution. I think that I have a good path now, heavily based on power query. The main approach is to have the GRAND TOTAL column (CN+CP+TRUCK) as data instead of dynamically calculated totals. Please, read TRUCK as what it was referred to as UNKNOWN in my original post.

 

This is achieved with something like this

- the main query is duplicated (using power query reference)

- in the duplicated query, the value of the MODE column is a constant text "TOTAL"

- the duplicated query is appended to the main query

- back to the main query, a conditional column named MODE ORDER is created. It maps CN to 1, CP to 2, TRUCK to 3 and TOTAL to 4

- column MODE is sorted by MODE ORDER

 

Once this is done, the matrix now feeds from the main table and the grand total is set to OFF for MODE. This way, the matrix seems like showing calculated grand totals when in fact is just showing data. 

 

There are a few drawbacks in this approach

- additional storage

- the fact that for any other visual that uses the same query I now need to filter out the lines in which MODE is "TOTAL" 

- the fact that there is no way to rename only the Total column more to the right as GRAND Total (because it is not in fact a grand total)

 

I am still working on it and I will review it with the client today.

 

Thanks for helping with this.

This is how the matrix looks like now, with the approach above.

Solution.png

v-kelly-msft
Community Support
Community Support

Hi  @rdaudt2021 ,

 

First create a dimension table as below:

v-kelly-msft_0-1616136928521.png

Then create a measure as below:

Measure = 
IF(MAX('Dimension table'[Category]) in FILTERS('Main Table'[Category]) && MAX('Dimension table'[subcategory]) in FILTERS('Main Table'[subcategory]),
CALCULATE(SUM('Main Table'[value]),FILTER(ALL('Main Table'),'Main Table'[Category]=MAX('Dimension table'[Category])&&'Main Table'[subcategory]=MAX('Dimension table'[subcategory]))),
IF(MAX('Dimension table'[subcategory])="Total"&&MAX('Dimension table'[Category]) in FILTERS('Main Table'[Category]),
CALCULATE(SUM('Main Table'[value]),FILTER(ALL('Main Table'),'Main Table'[Category]=MAX('Dimension table'[Category]))),
IF(MAX('Dimension table'[Category]) ="UNKNOWN+CN+CP",
CALCULATE(SUM('Main Table'[value]),FILTER(ALL('Main Table'),'Main Table'[subcategory]=MAX('Dimension table'[subcategory]))))))

And you will see:

v-kelly-msft_1-1616136965584.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

I checked it out this week. I am still trying to understand all that DAX. In the meantime, I verified that the numbers dont make much sense if I add additional lines of data to the Main table. I will explore it more and provide better feedback. Again, thank you very much.

Hi  @rdaudt2021 ,

 

Is your issue solved now?

If you cant get what you need,would you pls provide more sample data with expected output?

 

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

 

View solution in original post

Thanks! I really appreciate it. I am going to check it out and let you know how it work.

cheers!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors