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

     

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.