cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tonyclifton
Helper II
Helper II

Matrix Visual: creating custom Subtotal row

Hello community,

 

I am looking for a solution based on below screenshot. Is it possible to create a subtotal row only for "KPI_A" for each region with the given table structure?

2021-05-31 18_48_11-Window.png

 

Source:

RegionCompanyKPIValue
ACompAKPI_A1
ACompAKPI_B2
ACompBKPI_A11
ACompBKPI_B12
ACompCKPI_A21


Thank you.

1 ACCEPTED SOLUTION
PaulDBrown
Super User
Super User

You could create a custom layout. This blog post explains how to do it for columns, but you could apply the same method for rows.
How to create a custom or hybrod matrix 
I've also attached a file which has an example of a custom layout with two levels in the columns.





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

3 REPLIES 3
PaulDBrown
Super User
Super User

I would imagine you are going to need a SUMX() or SUMX(SUMMARIZE( )) to get the correct total





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






PaulDBrown
Super User
Super User

You could create a custom layout. This blog post explains how to do it for columns, but you could apply the same method for rows.
How to create a custom or hybrod matrix 
I've also attached a file which has an example of a custom layout with two levels in the columns.





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Thanks for the example @PaulDBrown. I will have to take a closer look at this.

In the meantime I managed to create a KPI that sums up "KPI A" so that I can show the correct value in the matrix.

However, the grand total value is now wrong (obviously).

Is there a way with DAX to show all the values but exclude some row values for the calculation of the total value?

If I just use the Calculate function with filter option and exclude the KPI "Total KPI A" it will create the correct grand total but it will also not display the amount for that row.

Thank you.

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors