Forum Discussion
Help with % measure
Hi,
I have a problem with this measure:
| Section | Question | Agree | Disagree | Neutral | Total Result |
| Our Strategy | Question A | 26 | 20 | 23 | 69 |
| Our Strategy | Question B | 65 | 1 | 3 | 69 |
| Our Strategy | Question C | 69 | 11 | 80 | |
| Our Strategy | Question D | 56 | 13 | 69 | |
| Total | 32 | 287 | |||
| Answer being calculated by measure | '32/218 | 14.68% | |||
| Answer should be: | '32/287 | 11.15% |
The numbers are numbers of responses. Because there are no responses to Question D for response 'Disagree', when it calculates the total % it is ignoring the 69 in the Question D Total Result row.
Do I need to add in SUMX or similar to the measure? Can anybody help please?
I tried with a sample data and simple calculation. It's getting as expected
Hi Fusilier ,
As Kaviraj11 said. You just need to create a simple dax expressions.Disagree percentage = DIVIDE( SUM('Table'[Disagree]), CALCULATE( SUM('Table'[Agree])+SUM('Table'[Disagree])+SUM('Table'[Neutral]), ALL('Table') ) )This will return the percentage of each row of data in the total data and provide the sum
Best regards,
Albert HeIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly
10 Replies
I tried with a sample data and simple calculation. It's getting as expected
Yes, it's ignoring the blank, update the dax to include the condition of ISBLANK or adjust with your query
%fromAll SBCLGA =
DIVIDE(
SUM('Raw Data'[row count]),
SUMX(
ALL('Raw Data'),
IF(ISBLANK('Raw Data'[row count]), 0, 'Raw Data'[row count])
),
0
)Hi,
Can you try with the measure below:
%fromAll SBCLGA =
DIVIDE(
SUM('Raw Data'[row count]),
SUMX(
ALL('Raw Data'[Answers aggregated]),
'Raw Data'[row count]
)
)Thanks for your help but it doesn't work. The measure won't accept 'row count' in the
SUMX(
ALL('Raw Data'[Answers aggregated]),
'Raw Data'[row count]
)Part of the measure.
'row count' is a calculated field here, so maybe that is the problem.
I've tried replacing that bit with a countrows measure, which works, but its still returning 14.68% instead of 11.15%.
Its still completely ignoring the Question D row total (69), I think because the Disagree response is nil.
My original measure works fine apart for rows with a null response
Scratching my head over this.
Hi! Fusilier
Your approach was correct but you can tweak the dax little bit. I have shared 2 sample dax below.
Div Test =DIVIDE( SUM('Table'[Disagree]) , CALCULATE(SUM('Table'[Total Result]) ) )
Divide Test =VAR _disagree = SUM('Table'[Disagree] )VAR _total = SUM('Table'[Total Result])VAR _division = DIVIDE( _disagree , _total , 0 )RETURN_divisionThank you for your reply. I think you're suggesting I create a table just for Disagree responses? I want to keep everything in the original table. My original measure was working apart for rows with null responses.
Hi! Fusilier
I just copy pasted the data from the table you shared in your post, can you paste the sample data if it doesn't look like this. My table looks like this:
and I have written those 2 measures to get the answer.
Div Test =DIVIDE( SUM('Table'[Disagree]) , CALCULATE(SUM('Table'[Total Result]) ) )
Divide Test =VAR _disagree = SUM('Table'[Disagree] )VAR _total = SUM('Table'[Total Result])VAR _division = DIVIDE( _disagree , _total , 0 )RETURN_divisionPlease share the sample data or pbix file without any pii information if this doesn't work.
Hi Fusilier ,
As Kaviraj11 said. You just need to create a simple dax expressions.Disagree percentage = DIVIDE( SUM('Table'[Disagree]), CALCULATE( SUM('Table'[Agree])+SUM('Table'[Disagree])+SUM('Table'[Neutral]), ALL('Table') ) )This will return the percentage of each row of data in the total data and provide the sum
Best regards,
Albert HeIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly
