Calculate measure based on row context and value of related column

[CloudHerder]

I have an assessment tool where respondents provide performance values for different KPIs. Based on collected responses, I then calculate 25th percentile, median, and 75th percentile scores. The issue is that for some KPIs, higher is better and for others, lower is better. In the KPI table, I include a T/F field named HigherBetter.

Using a matrix visual, I list KPIs by row. Columns are the respondent's region. The values are n (number of responses), 25th percentile, median, and 75th percentile. I have explicit measures for each value.

For KPIs where lower is better, I would like to invert the scale so that 75th percentile is a lower value than 25th percentile. I could do this by simply using 1 - [percentile]. To do this, I would need to look at the KPI value in each matrix row and look up the corresponding value of the HigherBetter column. Or perhaps there's a better way to accomplish my goal. But it has to be a dynamic measure based on the current row and filter context.

Here's a link to my sample .pbix file. 

Any help is greatly appreciated.

[OwenAuger · Accepted solution]

Hi @CloudHerder 

Yes, I agree, conditionally inverting the percentile would be the way to go 🙂

I would write the measures something like this (allowing for easy adjustment to handle different percentiles in the future):

25th Pctl =
VAR Pct = 0.25
-- Retrieve HigherBetter value from KPIs table, defaulting to TRUE
-- Default would only be used if multiple KPIs aggregated with different HigherBetter values which shouldn't happen.
VAR HigherBetter =
    SELECTEDVALUE ( KPIs[HigherBetter], TRUE () )
VAR PctAdj =
    IF ( HigherBetter, Pct, 1 - Pct )
RETURN
    PERCENTILE.INC ( Scores[Value], PctAdj )

75th Pctl =
VAR Pct = 0.75
-- Retrieve HigherBetter value from KPIs table, defaulting to TRUE
-- Default would only be used if multiple KPIs aggregated with different HigherBetter values which shouldn't happen.
VAR HigherBetter =
    SELECTEDVALUE ( KPIs[HigherBetter], TRUE () )
VAR PctAdj =
    IF ( HigherBetter, Pct, 1 - Pct )
RETURN
    PERCENTILE.INC ( Scores[Value], PctAdj )

Does this work as expected?

Regards 

[CloudHerder]

@OwenAuger Thank you so much! This is exactly what I was looking for. I didn't know that SELECTEDVALUE() could work this way. You've helped me before, and you are by far the most skillful person I've encountered in this forum. I truly appreciate it!