• 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


  • 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.


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



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?


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)

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



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
2. [Ceiling2] - This is for Yellow indicator limit
3. [KPI2] - This sets value to be used for indicator.
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.