Forum Discussion

rdaudt2021's avatar
rdaudt2021
Helper I
5 years ago
Solved

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

 

 

 

Need Help
  • v-kelly-msft's avatar
    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!

     

6 Replies

  • v-kelly-msft's avatar
    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:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

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

    • rdaudt2021's avatar
      rdaudt2021
      Helper I
      5 years ago

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

      cheers!

    • rdaudt2021's avatar
      rdaudt2021
      Helper I
      5 years ago

      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's avatar
        v-kelly-msft
        Community Support
        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!

         

  • rdaudt2021's avatar
    rdaudt2021
    Helper I
    5 years ago

    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's avatar
      rdaudt2021
      Helper I
      5 years ago

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