Help with calculating percentages based on total distinct count at row level

[Anonymous]

Hopefully someone can help - I am trying to calculate some percentages of anwers from some survey data.

The data is unpivoted and I wish to show the perctantage for each bullying behaviour.  I am able to do this when I use the distinct respondent count and then adjust the column to show as a percentage of grand total (highlighted in yellow), however when i attempt to write dax to achieve the same thing, it is giving me the percentage based on the totals at row level, not the overall total of 111.

This is the dax  measure I am using - 

% Bullying = DIVIDE([Respondent Distinct Count],CALCULATE([Countrows](Bullying)))

(The reason I need to write a dax measure is that i then need to calculate differences compared to a previous data set)

Help 🙂

[Dangar332 · Accepted solution]

Hi, @Anonymous 

try below

result =
DIVIDE(
    [Respondent Distinct Count],
    CALCULATE(
        [Respondent Distinct Count], 
       removefilters('tablename'[bullying behaviour])
    )
)

[Anonymous]

Thanks for your help, adding in the remove filters to the DAX did the trick! 🙂

[FreemanZ]

hi @Anonymous ,

try like:

% Bullying = 

DIVIDE(

    [Respondent Distinct Count],

    CALCULATE(

        Countrows(Bullying), 

        ALL(Bullying)

    )

)

[Anonymous]

Hi @FreemanZ 

Thank you for your suggestion - unfortunately its still not givng me the answer - its dividing by 341, which is the number of rows, not the distinct respondent count (111) - see below

 

[FreemanZ · Accepted solution]

aha, try like:

try like:

% Bullying = 

DIVIDE(

    [Respondent Distinct Count],

    CALCULATE(

         [Respondent Distinct Count],

        ALL(Bullying)

    )

)

[Dangar332 · Accepted solution]

Hi, @Anonymous 

try below

result =
DIVIDE(
    [Respondent Distinct Count],
    CALCULATE(
        [Respondent Distinct Count], 
       removefilters('tablename'[bullying behaviour])
    )
)