Divide a column by itself with filters

[Anonymous]

Hi guys,

Wondering if you can assist. I have a table with a column called Result. 

Within the Result column, there are values such as:

Satisfactory

Did Not Attend

Poor

Excellent

I would like to create a measure that allows me to see the % of those who achieved 'Satisfactory' / over everyon who sat the exam. As there are values of Did not Attend, I will need to filter that out when dividing. 

SUM 'RESULT' WITH VALUE = SATISFACTORY / SUM ' RESULT' SATISFACTORY &  POOR & EXCELLENT.

How can i do this, because I keep coming up with errors. I have tried SUM and COUNT but cannot get my head around the filtering part.

Any guidance would be much apprecaited. 

[vanessafvg]

@Anonymous  create a measure

% of those who achieved 'Satisfactory' =
VAR satistfatory =
    CALCULATE ( COUNTROWS ( tablename ), result = "satisfactory" )
VAR all =
    CALCULATE ( COUNTROWS ( tablename ) )
RETURN
    DIVIDE ( satsifactory, all )

is one way of doing it

[Anonymous]

But we cannot divide by all, we need to filter out the do not attend. 

[Anonymous · Accepted solution]

@Anonymous -

The following divides the selected result by all of the results, except for "Did Not Attend":

% Selected Result = 
DIVIDE(
    COUNTROWS(Exam),
    CALCULATE(
        COUNTROWS(Exam),
        Exam[Result] <> "Did Not Attend"
    )
)

The following divides "Satisfactory" in particular by everything except "Did Not Attend":

% Selected Result = 
DIVIDE(
    CALCULATE(
        COUNTROWS(Exam),
        Exam[Result] = "Satisfactory"
    ),
    CALCULATE(
        COUNTROWS(Exam),
        Exam[Result] <> "Did Not Attend"
    )
)

The second parameter of "CALCULATE" changes the "filter context".

Hope this helps,

Nathan

[Anonymous]

Excellent, thanks a lot. This worked.

If i wanted to add another condition to the Does Not Contain, what is best practise?

Can I do as below?

& "Value"

[Anonymous]

@Anonymous  -

You could do the following:

% Selected Result = 
DIVIDE(
    COUNTROWS(Exam),
    CALCULATE(
        COUNTROWS(Exam),
        NOT Exam[Result] IN {"Did Not Attend", "Satisfactory"}
    )
)