Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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] )
)
Proud to be a Super User!
Hi @crln-blue ,
Try this:
Formula =
IF (
NOT ( HASONEVALUE ( 'Table'[Category] ) ),
CALCULATE (
SUM ( 'Table'[Value] ),
'Table'[Category] = "Average of All Category"
),
SUM ( 'Table'[Value] )
)
Proud to be a Super User!
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.
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.
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
104 | |
77 | |
67 | |
63 |
User | Count |
---|---|
144 | |
107 | |
105 | |
82 | |
69 |