Using selected value to return teams figures

[DRossi]

Hello,

I have report that uses a slicer to select an individual engineer and show their KPIs.

I have a bar chart that shows their performance. I also then created a measure to show the national average (using the ALL function to remove the engineer slicer). Please see below.

What I want to do is also return the engineers team average as another measure. So I created a new group and input the teams and called the column 'Engineer Hierarchy'. I tried modifying the below code using SELECTEDVALUE but couldn't get it to work.

Any ideas?

National FTF = 
VAR __BASELINE_VALUE =
    CALCULATE(
        DISTINCTCOUNT('Query1'[FA_ID]), ALL(Query1[ENGINEER]),
        'Query1'[FA_TYPE_CD] IN { "AS_RPPT" }
    )
VAR __MEASURE_VALUE = CALCULATE(
COUNTA('Query1'[FA_TYPE_CD]), ALL(Query1[ENGINEER]),
'Query1'[FA_TYPE_CD] IN { "BS_BKCT","BS_BKCO","AS_RPFR","AS_RPHB","AS_RPNH","AS_RPSV","AS_WNSA","AS_WAPT","AS_SVFR", "AS_WAHB"})


RETURN

    IF(
        NOT ISBLANK(__MEASURE_VALUE),
        1 -
 DIVIDE(__BASELINE_VALUE, __MEASURE_VALUE)
 
)

[v-lionel-msft]

Hi @DRossi ,

Could you show me a sample data and  use a table to show the measure results you eventually want to return. You can make an example return table in excel.

Best regards,
Lionel Chen

[DRossi]

Hi @v-lionel-msft .

I've got this far. The forumla is trying to ignore all filters for [ENGINEER] but look up where the ENGINEER is in [Engineer Hierarchy] and return all for that (there's five teams the engineer could possibly be in which I created in groups). The idea is to have a way to identify when I have selected an engineer in the slicer, show his stats (i've got that formula), compared against national stats (I've got that formula) and compared to his team mates.

So ultimately at the end, have a bar chart with three columns (his, national and team) which allows for a comparison.

Example

John Smith	58%
National	78%
Team	77%

Team FTF = 
VAR __BASELINE_VALUE =
    CALCULATE(SELECTEDVALUE(Query1[Engineer Hierarchy], "0"),
        DISTINCTCOUNT('Query1'[FA_ID]), ALL(Query1[ENGINEER]),
        'Query1'[FA_TYPE_CD] IN { "AS_RPPT" }
    )
VAR __MEASURE_VALUE = CALCULATE(SELECTEDVALUE(Query1[Engineer Hierarchy], "0"),
COUNTA('Query1'[FA_TYPE_CD]), ALL(Query1[ENGINEER]),
'Query1'[FA_TYPE_CD] IN { "BS_BKCT","BS_BKCO","AS_RPFR","AS_RPHB","AS_RPNH","AS_RPSV","AS_WNSA","AS_WAPT","AS_SVFR", "AS_WAHB"})


RETURN

    IF(
        NOT ISBLANK(__MEASURE_VALUE),
        1 -
 DIVIDE(__BASELINE_VALUE, __MEASURE_VALUE)
 
)

 

[DRossi]

I can't see where to attach example dataset