Sep 29 2020 11:39 PM
I was wondering if the KPI Status can be included for slicer tool filtering in the pivot table.
Please help
Oct 04 2020 10:39 AM
Oct 04 2020 11:54 AM
I don't think so. KPI Status is actually the measure, you can't add slicer on measure.
Oct 04 2020 10:39 PM
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.
Oct 05 2020 02:44 AM - edited Oct 06 2020 03:09 AM
@Excel ,,
Possibly this may help you.
Source Tale:
N.B.
=IF(W2>=6000,"Green",IF(W2>=1500,"Yellow",IF(W2<1500,"Red")))
Pivot Table & Slicer:
How it works:
3. In Data section, select Refresh Data When Open file.
4. Now create Slicer and from it's setting, set column to 3.
Now this is the tricky part.
3. Now copy Condition Formatting on W23 and W25:W30.
Now start clicking slicer buttons to filter data.
:Green:
:Yellow:
:Red:
Oct 05 2020 07:44 AM
@Ramiz_Assaf , @Rajesh_Sinha ,
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.
Oct 06 2020 03:15 AM - edited Oct 06 2020 03:16 AM
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.