Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a KPI on a sheet that I want to have and keep the value for a specific set modifier no matter what is selected on the sheet. Example:
So here, the large value will be the Site, the small value should be the average of All sites (There are 4 KPIs one for each site.)
This KPI has the following Measures:
Average(for specific site):
Avg(Aggr({<PLANT_CODE={'BET'}>} //Site location code
Floor(Only(
{<[Process Code]-={'CC','CP','BS'},[Tread]-={'POOL','REPONLY'},[Casing Status]-={'DELETED'}>}[Completed Date]))
-
Floor(Only(
{<[Casing Status]-={'DELETED'}, [gra_audit_trail.POST_NAME]={'RECEIVING'},ACTION_NAME={'SHIP_RECEIVING'}>}[Audit Create]))
,[Tag #]))
This Seems to work, except that when I select a filter item ie Year, Customer, Plant Name from the filter bar(below),
the KPI values change.
I want it to always reflect the average for the site code selected and I can't seem to make this happen!
Any help or insights are appreciated!
Did you put 1 in all 4 places, including for the Avg function?
Avg({1} Aggr({1<PLANT_CODE={'BET'}>} //Site location code
Floor(Only(
{1<[Process Code]-={'CC','CP','BS'},[Tread]-={'POOL','REPONLY'},[Casing Status]-={'DELETED'}>}[Completed Date]))
-
Floor(Only(
{1<[Casing Status]-={'DELETED'}, [gra_audit_trail.POST_NAME]={'RECEIVING'},ACTION_NAME={'SHIP_RECEIVING'}>}[Audit Create]))
,[Tag #]))
Try using {1} as part of you set analysis statement.
Yeah, I've tried that, doesn't seem to work.
Did you put 1 in all 4 places, including for the Avg function?
Avg({1} Aggr({1<PLANT_CODE={'BET'}>} //Site location code
Floor(Only(
{1<[Process Code]-={'CC','CP','BS'},[Tread]-={'POOL','REPONLY'},[Casing Status]-={'DELETED'}>}[Completed Date]))
-
Floor(Only(
{1<[Casing Status]-={'DELETED'}, [gra_audit_trail.POST_NAME]={'RECEIVING'},ACTION_NAME={'SHIP_RECEIVING'}>}[Audit Create]))
,[Tag #]))
Thank you! Adding the {1} to the AVG function did the trick.