Filtered/Average Lookup

Marmaduke0703

New Member
Joined
Jan 25, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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)

LUTbl_TargetsDispatch.png


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.

Calculated table.png


Any suggestions would be greatly appreciated!

Thanks,
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Marmaduke0703

New Member
Joined
Jan 25, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 25, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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")
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,697
Messages
5,637,864
Members
416,986
Latest member
zmartee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top