Forum Discussion

crln-blue's avatar
crln-blue
Post Patron
2 years ago
Solved

Custom matrix row subtotal

Hello! 

 

I have a matrix table with the following display:

My matrix table is pretty straightforward since I already adjusted the calculations. I turned off the subtotals option since I don't need it. However, when I minimize the Year, it still gives the subtotal that is automatically calculated by PowerBI:

I'm aware that I can adjust the formatting on the settings to make it blank but I would like to know if there are ways where I can control the display of the row subtotal? iLike a custom row subototal for the matrix table. The correct row subtotal are the values under the "Average of All Category" category of each year.

I checked for different solutions online and tried using HASONVALUE and ISINSCOPE but it doesn't display the value I wanted to.

 

Below is my sample data:

YearCategoryStatusValue
20231000Breakdown3
20231000Creation31
20231000Release15
20231000Approval59
20232000Breakdown1
20232000Creation5
20232000Release 
20232000Approval19
20233000Breakdown1
20233000Creation2
20233000Release11
20233000Approval1
20233000Breakdown2
20234000Creation1
20234000Release3
20234000Approval7
2023Average of All CategoryBreakdown3
2023Average of All CategoryCreation30
2023Average of All CategoryRelease13
2023Average of All CategoryApproval56
20241000Breakdown1
20241000Creation6
20241000Release138
20241000Approval2
20242000Breakdown3
20242000Creation10
20242000Release4
20242000Approval2
20243000Breakdown3
20243000Creation8
20243000Release26
20243000Approval1
20244000Breakdown1
20244000Creation1
20244000Release9
20244000Approval1
2024Average of All CategoryBreakdown2
2024Average of All CategoryCreation8
2024Average of All CategoryRelease124
2024Average of All CategoryApproval3

 

Thank you very much for the help!

Need Help
  • danextian's avatar
    danextian
    2 years ago

    Hi crln-blue ,

     

    Try this:

    Formula =
IF (
    NOT ( HASONEVALUE ( 'Table'[Category] ) ),
    CALCULATE (
        SUM ( 'Table'[Value] ),
        'Table'[Category] = "Average of All Category"
    ),
    SUM ( 'Table'[Value] )
)

     

3 Replies

  • danextian's avatar
    danextian
    Super User
    2 years ago

    Hi crln-blue ,

     

    Try this:

    Formula =
IF (
    NOT ( HASONEVALUE ( 'Table'[Category] ) ),
    CALCULATE (
        SUM ( 'Table'[Value] ),
        'Table'[Category] = "Average of All Category"
    ),
    SUM ( 'Table'[Value] )
)

     

    • crln-blue's avatar
      crln-blue
      Post Patron
      2 years ago

      Thank you! It works! I'd like to ask, why the HASONEVALUE is used in Category and not Year? I first used it on Year but it doesn't get the right results.

      • danextian's avatar
        danextian
        Super User
        2 years ago

        To illustrate, year 2023 will always return as a single value for every item within its own group so if HASONEVALUE is applied to year, it will always return true. Ther are more than one category items at the year hierarchy level so using NOT(HASONEVALUE(table[category])) will return true only at that instance then  (except if there really is just one  category item  for that year) and false for every category item.