Help with % measure

[Fusilier]

Hi,

I have a problem with this measure:

%fromAll SBCLGA = DIVIDE( SUM('Raw Data'[row count]),

CALCULATE(SUM('Raw Data'[row count]),

all('Raw Data'[Answers aggregated])))

The table below illustrates what is happening:

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?

[Kaviraj11 · Accepted solution]

I tried with a sample data and simple calculation. It's getting as expected

[Anonymous · Accepted solution]

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 He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Kaviraj11 · Accepted solution]

I tried with a sample data and simple calculation. It's getting as expected

[Fusilier]

Thank you so much. All working fine now. Really appreciate your help with this.

[Fusilier]

Hi thank you for taking the time to help.

Excuse my ignorance.

Are you saying I should create three new tables? One each for:

Agree

Disagree

Neutral

Thanks

[Kaviraj11]

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
)

[AnkitKukreja]

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

_division

[Fusilier]

Thank 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.

[AnkitKukreja]

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

_division

Please share the sample data or pbix file without any pii information if this doesn't work.

[Kaviraj11]

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]
)
)

[Fusilier]

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.