Forum Discussion

crln-blue's avatar
crln-blue
Icon for Post Patron rankPost 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
Icon for Super User rankSuper 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
Icon for Post Patron rankPost 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
    Icon for Super User rankSuper 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.