cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DRossi Helper I
Helper I

Using selected value to return teams figures

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)
 
)

 

 

 

3 REPLIES 3
Microsoft v-lionel-msft
Microsoft

Re: Using selected value to return teams figures

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 Helper I
Helper I

Re: Using selected value to return teams figures

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 Smith58%
National 78%
Team77%

 

 

 

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)
 
)

 

Capture.PNG 

Highlighted
DRossi Helper I
Helper I

Re: Using selected value to return teams figures

I can't see where to attach example dataset

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors