Custom matrix row subtotal

[crln-blue]

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:

Year	Category	Status	Value
2023	1000	Breakdown	3
2023	1000	Creation	31
2023	1000	Release	15
2023	1000	Approval	59
2023	2000	Breakdown	1
2023	2000	Creation	5
2023	2000	Release
2023	2000	Approval	19
2023	3000	Breakdown	1
2023	3000	Creation	2
2023	3000	Release	11
2023	3000	Approval	1
2023	3000	Breakdown	2
2023	4000	Creation	1
2023	4000	Release	3
2023	4000	Approval	7
2023	Average of All Category	Breakdown	3
2023	Average of All Category	Creation	30
2023	Average of All Category	Release	13
2023	Average of All Category	Approval	56
2024	1000	Breakdown	1
2024	1000	Creation	6
2024	1000	Release	138
2024	1000	Approval	2
2024	2000	Breakdown	3
2024	2000	Creation	10
2024	2000	Release	4
2024	2000	Approval	2
2024	3000	Breakdown	3
2024	3000	Creation	8
2024	3000	Release	26
2024	3000	Approval	1
2024	4000	Breakdown	1
2024	4000	Creation	1
2024	4000	Release	9
2024	4000	Approval	1
2024	Average of All Category	Breakdown	2
2024	Average of All Category	Creation	8
2024	Average of All Category	Release	124
2024	Average of All Category	Approval	3

Thank you very much for the help!

[danextian · Accepted solution]

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]

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]

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.