#3 Followup dynamic slicer on dimension table for Report agains data in fact table.

[_google]

Hi Ninja's

This is follow-up, following solution view provided by Ninja -Chiriho for earlier query ,Help to create data model in Powerpivot for Flat table (fact table).

Using the guidance provided earlier , have reach in the good shape on complex calculation to arrived required measure on real time data.

However am struggling how to use the dynamic filter ( slicer input) to generate KPI report which is based on values from the dimension table.

Please find the attached file which give view on the report required against the KPI tolerance table.

What i have tried in real time data file, using the calculate function have pulled measure in the measure ( low and high in this example ) in dimension table however am not able to put row context for the filed able in fact table ( Area or Region in this example)

Appreciate in advance for help and guidance

 

[_google]

Hi

do we have suggestions on the query! or i need to provide additional information.

regards
Kumar

 

[Chihiro]

It isn't clear what your expected output should look like.

What you have in "required report2" isn't suitable for PivotTable.

What exactly are you after?

 

[_google]

Hi Chihiro

Hopefully, i be able to clarify

1. In 1st screen image KPI status is derived on one value across all Zone- attributes to identify Green, Amber,Red based KPI% ( if <10% G, 10% ~ 50% A, >50%R)
image


2. However what business has, for each attribute of Zone a different KPI tolerance limit on % of lows
e.g Zone -AAA % of =[Lows]/([Lows]+[Highs]) = 10%, if <5% -G, if 5%~10% A and >10%R but then for Zone-BBB will have different limit, so on for others

don't know if this can be managed through powerpivot or explained it better

 

[Chihiro]

So it's target that you need changed for each and not the calculation for %?

Then you would need to add few more measures.

1. [Ceiling1] - This is for Green indicator limit

Ceiling1:=SWITCH(TRUE(),FIRSTNONBLANK(fTable[Zone],1)="AAA",0.05,FIRSTNONBLANK(fTable[Zone],1)="BBB",0.2,FIRSTNONBLANK(fTable[Zone],1)="CCC",0.01,FIRSTNONBLANK(fTable[Zone],1)="DDD",0.25,0)

2. [Ceiling2] - This is for Yellow indicator limit

Ceiling2:=SWITCH(TRUE(),FIRSTNONBLANK(fTable[Zone],1)="AAA",0.1,FIRSTNONBLANK(fTable[Zone],1)="BBB",0.5,FIRSTNONBLANK(fTable[Zone],1)="CCC",0.025,FIRSTNONBLANK(fTable[Zone],1)="DDD",0.5,0)

3. [KPI2] - This sets value to be used for indicator.

KPI2:=SWITCH(True(),[KPI]<[Ceiling1],1,[KPI]<=[Ceiling2],2,3)

Then KPI gets set like below.


See attached. I added all the measures in the table for demonstration. But you can remove the ones you don't need.

 

[_google]

Hi Chihiro,

Thanks again, will do the testing over weekend and revert.