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...

Need Help

v-kelly-msft
5 years ago
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!

v-kelly-msft

Community Support

5 years ago

Hi rdaudt2021 ,

First create a dimension table as below:

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: