• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

_google

New Member
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
 

Attachments

Chihiro

Excel Ninja
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

New Member
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
upload_2018-5-4_14-34-59.png

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

upload_2018-5-4_14-33-40.png
 

Chihiro

Excel Ninja
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
Code:
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
Code:
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.
Code:
KPI2:=SWITCH(True(),[KPI]<[Ceiling1],1,[KPI]<=[Ceiling2],2,3)
Then KPI gets set like below.
upload_2018-5-4_11-18-15.png

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

Attachments

Top