Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
)
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
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)
)
I can't see where to attach example dataset
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |