Survey - How to create '% of Total for Questions' measure?

[Ouarda]

Hi, I'm analysing a survey and I need to create a new measure for % of Total for Questions so that it would show me the percentage of all answers for THIS question (not ALL questions).

This is what I have:

and this is what I need (it's a screenshot from a youtube tutorial):

I know she has a dax formula in there but I can't figure out how to adjust it to my columns.

Any help would be greatly appreciated, I have spent too much time trying to solve it and I really need to move on now.

[Anonymous · Accepted solution]

Hi @Ouarda ,

If I understand you wrongly, please provide simple data or a .pbix file without sensitive data.

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

The Table data is shown below:

Use the following DAX expression to create a measure

Measure = 
VAR _total_responses_for_one_question = CALCULATE(SUM('All Suveys Data'[Count of responses]),ALL('All Suveys Data'[Answer Options]))
VAR _response = SELECTEDVALUE('All Suveys Data'[Count of responses])

RETURN DIVIDE(_response,_total_responses_for_one_question)

Final output

Best Regards,
Wenbin Zhou

[mark_endicott · Accepted solution]

@Ouarda - Here's the DAX you need for your table, please put it into a measure:

VAR _count =
    SUM ( 'All Surveys Data'[Count of responses] )
VAR _question =
    SELECTEDVALUE ( 'All Surveys Data'[Question] )
VAR _responses =
    CALCULATE (
        _count,
        REMOVEFILTERS ( 'All Surveys Data' ),
        'All Surveys Data'[Question] = _question
    )
RETURN
    DIVIDE ( _count, _responses )

This works because the _count variable is filtered by the Filter Context of the 'Answer Options' column, whereas the _responses variable has this context removed, and the context of the question total is applied instead. 

If this works for you, please accept as the solution for the visibility of others. 

[Anonymous · Accepted solution]

Hi @Ouarda ,

If I understand you wrongly, please provide simple data or a .pbix file without sensitive data.

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

The Table data is shown below:

Use the following DAX expression to create a measure

Measure = 
VAR _total_responses_for_one_question = CALCULATE(SUM('All Suveys Data'[Count of responses]),ALL('All Suveys Data'[Answer Options]))
VAR _response = SELECTEDVALUE('All Suveys Data'[Count of responses])

RETURN DIVIDE(_response,_total_responses_for_one_question)

Final output

Best Regards,
Wenbin Zhou