Kpi status with slicer

%3CLINGO-SUB%20id%3D%22lingo-sub-1727638%22%20slang%3D%22en-US%22%3EKpi%20status%20with%20slicer%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1727638%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22m7.PNG%22%20style%3D%22width%3A%20532px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F222992i952F299A5643B755%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22m7.PNG%22%20alt%3D%22m7.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20was%20wondering%20if%20the%20KPI%20Status%20can%20be%20included%20for%20slicer%20tool%20filtering%20in%20the%20pivot%20table.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EPlease%20help%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1727638%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1743578%22%20slang%3D%22en-US%22%3ERe%3A%20Kpi%20status%20with%20slicer%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1743578%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F811137%22%20target%3D%22_blank%22%3E%40Zan_Hanifee%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20add%20a%20slicer%20from%20the%20pivot%20table%20menu%20in%20the%20ribbon%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eplease%20see%20the%20attached%20file%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1743655%22%20slang%3D%22en-US%22%3ERe%3A%20Kpi%20status%20with%20slicer%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1743655%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F811137%22%20target%3D%22_blank%22%3E%40Zan_Hanifee%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20don't%20think%20so.%20KPI%20Status%20is%20actually%20the%20measure%2C%20you%20can't%20add%20slicer%20on%20measure.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1744196%22%20slang%3D%22en-US%22%3ERe%3A%20Kpi%20status%20with%20slicer%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1744196%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F811137%22%20target%3D%22_blank%22%3E%40Zan_Hanifee%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20understood%20your%20request%2C%20you%20can%20work%20around%20it%20by%201st%20ranking%20the%20original%20data%20using%20if%20condition%2C%20then%20you%20can%20create%20your%20slicer.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20this%20example%2C%20I%20labeled%20the%20data%20I%20have%20based%20on%20ranking%2C%201st%20400%20cities%20got%20%22Green%22%20then%20%22Yellow%22%20then%20%22Red%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20just%20add%20the%20slicer.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Frequent Contributor

m7.PNG

 

I was wondering if the KPI Status can be included for slicer tool filtering in the pivot table.

Please help

6 Replies

@Zan_Hanifee 

 

You can add a slicer from the pivot table menu in the ribbon

 

please see the attached file

 

@Zan_Hanifee 

I don't think so. KPI Status is actually the measure, you can't add slicer on measure.

@Zan_Hanifee 

 

I understood your request, you can work around it by 1st ranking the original data using if condition, then you can create your slicer. 

 

In this example, I labeled the data I have based on ranking, 1st 400 cities got "Green" then "Yellow" then "Red"

 

Then just add the slicer.

 

 

@Zan_Hanifee ,,

Possibly this may help you.

 

Source Tale:

 

Rajesh-S_0-1601889339451.png

 

N.B.  

  1. Color is Helper Column, for neatness later on you may hide it.
  2. Formula in cell X2, fill it down.

=IF(W2>=6000,"Green",IF(W2>=1500,"Yellow",IF(W2<1500,"Red")))

 

Pivot Table & Slicer:

 

Rajesh-S_0-1601889698642.png

 

How it works:

  1. Create PT, put City Name & Color in Row, and Sum Of Freight as Value.
  2. Select PT, Right click and from Pivot Tale Option dialogue, hit Display and un-check, Show Expand Collapse button.

     3.  In Data section, select Refresh Data When Open file.

     4.  Now create Slicer and from it's setting, set column to 3. 

 

  • You may keep as original also.

     Now this is the tricky part. 

  1. Apply Condition Formatting to each part separably. 
  2. Select W18:W21 and apply ICON Set using following rules as show below.
 

Rajesh-S_0-1601890739337.png

 

    3. Now copy Condition Formatting on W23 and W25:W30.

 

  • Reason for doing this is to skip ROW Totals while Filter with Slicer.

 

Now start clicking slicer buttons to filter data.

 

:Green:

 

Rajesh-S_1-1601890965526.png

 

:Yellow:

 

Rajesh-S_2-1601891056166.png

 

:Red:

 

Rajesh-S_3-1601891110923.png

 

 

@Ramiz_Assaf , @Rajesh-S ,

IMHO, such workarounds are quite limited since they are based on source data, not on calculated values as data model KPI. For example, if we have yellow for +-20 of average, red for below and green for upper values, this status will be shown not on only on all cities, but on filtered context as well. If we filter by country, that will be the status for this country average. Or for any filter we will apply.

KPI should use basic as well calculated data,, and sometimes to fix the issue we have to follow non traditional methods, and that's what I did.

I've applied ICON sets on numeric column,, and while filter usually adjacent value appears, nothing new,, the method I've used address the core issue,, and it was only my intentions.