Forum Discussion
Helper IIIIssue with Displaying Correct Subtotals in Matrix Visual
Hello,
I'm seeking assistance with a matrix visual in Power BI where I need to use a specific column as a value without aggregation. Despite my efforts, the subtotals are not displaying as expected.
Background
In my 'Table_2', there's a column named (0-1) that I specifically want to display as it is, without any aggregation, in a matrix visual. To ensure that Power BI displays the exact values from this column without summing them up or applying any other aggregate functions,
I've created a measure with the following DAX formula:
0-1 Measure=
VAR SelectedValue = SELECTEDVALUE('Table_2'[(0-1)])
RETURN IF(ISBLANK(SelectedValue), 0, SelectedValue)
Sample Input Data
Below is a sample of the data from 'Table_2':
| Names | (0-1) |
| AA | 200 |
| BB | 300 |
| CC | 400 |
| DE | 300 |
Matrix Visual Configuration
- Rows: Names
- Values: The measure based on the (0-1) column
Current Output with Issue
The matrix correctly displays the individual (0-1) values for each ID, but the subtotal is incorrectly showing as 0.
Matrix Visual:
| Names | (0-1) |
| AA | 200 |
| BB | 300 |
| CC | 400 |
| DE | 300 |
| Subtotal | 0 |
Desired Output
I need the subtotal to accurately sum the individual (0-1) values:
| Names | (0-1) |
| AA | 200 |
| BB | 300 |
| CC | 400 |
| DE | 300 |
| Subtotal | 1200 |
The Challenge
How can I modify my measure or adjust the matrix settings to ensure that the subtotals correctly calculate the sum of the (0-1) values at each hierarchy level?
I would greatly appreciate any advice or solutions that could help me resolve this issue with the subtotals. Thank you for your time and assistance!
Warm regards,
SBC
Hi SBC
I am not sure if this meets your requirement, but I thought as the selectedvalue is a syntax suger for if-hasonevalue-values, if you put the sum in the if conditional branch, you can get the total for the values where the values are multiple.
Best regards,
7 Replies
- DataNinja777
Super User
Hi SBC ,
Please try Sumxing over the table of values('Table_2'[Names]) to get the correct subtotal:
Best regards,
SBCHi DataNinja777 , Thanks for your response. I tried using SUMX as you suggested, but unfortunately, it didn't work as expected. I'm still getting the same values and the subtotals are not calculating correctly.Could you please provide further guidance or an alternative solution?
Thanks again for your help.
Regards,
SBC
- DataNinja777
Hi SBC ,
You are right. It shows zero even with sumx. Is there any reason why you have to use selectedvalue? Is it not sufficient to use the usual sum function for 'Table_2'[(0-1)]?
Best regards,
- DataNinja777
Hi SBC
I am not sure if this meets your requirement, but I thought as the selectedvalue is a syntax suger for if-hasonevalue-values, if you put the sum in the if conditional branch, you can get the total for the values where the values are multiple.
Best regards,
- Greg_Deckler
Community Champion
SBC First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8- SBC
Hi Greg_Deckler ,
Thanks for your response,
I've attempted to implement the logic from the link you provided, but unfortunately, it's not yielding the expected results.
Thanks,
SBC
