Matrix - Subtotal across columns

[rdaudt2021]

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

[rdaudt2021]

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.

[rdaudt2021]

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

[v-kelly-msft]

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:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

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

[rdaudt2021]

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.

[v-kelly-msft · 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!

[rdaudt2021]

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

cheers!