topic Issue with Displaying Correct Subtotals in Matrix Visual in DAX Commands and Tips

Issue with Displaying Correct Subtotals in Matrix Visual

SBC — Sun, 28 Apr 2024 14:34:11 GMT

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

Re: Issue with Displaying Correct Subtotals in Matrix Visual

DataNinja777 — Sun, 28 Apr 2024 15:15:43 GMT

Hi SBC ,

Please try Sumxing over the table of values('Table_2'[Names]) to get the correct subtotal:

Best regards,

Re: Issue with Displaying Correct Subtotals in Matrix Visual

Greg_Deckler — Sun, 28 Apr 2024 17:04:45 GMT

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

Re: Issue with Displaying Correct Subtotals in Matrix Visual

SBC — Mon, 29 Apr 2024 03:17:39 GMT

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

Re: Issue with Displaying Correct Subtotals in Matrix Visual

SBC — Mon, 29 Apr 2024 04:06:17 GMT

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

Re: Issue with Displaying Correct Subtotals in Matrix Visual

DataNinja777 — Mon, 29 Apr 2024 13:00:29 GMT

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,

Re: Issue with Displaying Correct Subtotals in Matrix Visual

SBC — Mon, 29 Apr 2024 13:16:05 GMT

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

Re: Issue with Displaying Correct Subtotals in Matrix Visual

DataNinja777 — Mon, 29 Apr 2024 14:03:11 GMT

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,