Filtered/Average Lookup

[Marmaduke0703]

Hi,

I’m fairly new to Power BI so still figuring out measures and calculated columns etc. Here’s what I'm trying to do:

• I have two sites that are measured on a number of KPI’s
• Each site has its own set of target levels (with all sites stored in one single table – first image below)
• And each KPI is broken into tiers/grade boundaries (Attribute column).

I’m trying to set up a measure that will lookup the performance tier/grade each week for a calculated KPI. I also need to be able to take an average of the sites target levels when more than one site is selected in the report slicer.

So far, I’ve managed to get it working on a single site selection with different target levels using a MAXX & filter combination. However this fails when I try to look at multiple sites (when it should take an average). I’m assuming this is because there are duplicate values just a bit out of sync with the grades? (e.g. 89.5% = T-2 for site 1 or T0 for site 2)

I’ve currently come to the conclusion I need to create a calculated table or column (something like the below where LUTbl_TargetDispatch is the table above) and lookup from there to avoid the multiple grade entries however I can’t work out how to link this to the site filter on my report page. At the moment these values do not change on site selection.

Any suggestions would be greatly appreciated!

Thanks,

 

[Marmaduke0703]

On further reading, it doesn't look like it's possible to dynamically filter the calculated table to so I'm back to trying to write a measure

 

[Marmaduke0703]

Managed to fine a solution that worked - there's probably an easier way but it works :D

Step 1 - for each grade/tier, write a measure with AVERAGEX(FILTER...) to create the average of each tier (there is a relationship to the site filter); e.g. T1 Disp = Averagex(Filter(LUTbl_TargetsDispatch,[Attribute]="T1"),LUTbl_TargetsDispatch[Value])


Step 2 - Write a measure using SWITCH to determine which Tier to show; e.g.
TierMeasure =
SWITCH(
true(),
[Dispatch]>=[T3 Disp],"T3",
[Dispatch]>=[T2 Disp],"T2",
[Dispatch]>=[T1 Disp],"T1",
[Dispatch]>=[T0 Disp],"T0",
[Dispatch]>=[T-1 Disp],"T-1",
[Dispatch]>=[T-2 Disp],"T-2",
[Dispatch]>=0,"T-3")