Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to Solution.
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,
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,
@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
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
Hi @SBC ,
Please try Sumxing over the table of values('Table_2'[Names]) to get the correct subtotal:
Best regards,
Hi @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
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,
Hi @DataNinja777 ,
Thanks for your question! I'm using SELECTEDVALUE because I need to display the exact values from the '0-1' column in a matrix visual without any aggregation. Directly placing the column in the value field defaults to aggregation like sum or max, which isn't suitable for my needs. By converting the column into a measure using SELECTEDVALUE, I can control the output to show individual values as they are. The measure ensures that if there's no value selected, it returns 0, maintaining consistency in the visual display.
However, I'm facing an issue where I can display the values correctly, but I'm unable to sum these values across the visual.
Any suggestions on how to achieve this would be greatly appreciated!
Best,
SBC
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
71 | |
43 | |
21 | |
21 | |
14 |
User | Count |
---|---|
124 | |
42 | |
39 | |
28 | |
24 |