Forum Discussion

Helper III

9 years ago

Solved

Conditional Formatting Slicer

Hi All, It may be confusing the way i'm explaining, but the requirement is like below.. I have source in excel which has data in region wise Actual and Target for the whole year of 2017.. we ...

Need Help

Tips and Tricks

v-sihou-msft
9 years ago
Thiyagu

Firstly, you should pivot your months into one column:

Then create a calculated column to assign value (1 or 0) when achieving target or not.

Tag = var CurrentTarget = LOOKUPVALUE('KPI'[Value],'KPI'[KPI Name],'KPI'[KPI Name],'KPI'[Year],'KPI'[Year],'KPI'[Month],'KPI'[Month],'KPI'[ACT/TARGET],"TARGET") return SWITCH('KPI'[Green],"Lower",IF('KPI'[Value]<CurrentTarget,1,0),"Higher",IF('KPI'[Value]>CurrentTarget,1,0))

Now you can create a matrix and apply conditional formatting on Tag column.

You can apply a slicer on Tag column to filter Red or Green.

Regards,

v-sihou-msft

Microsoft Employee

9 years ago

Thiyagu

Firstly, you should pivot your months into one column:

Then create a calculated column to assign value (1 or 0) when achieving target or not.

Tag = 
var CurrentTarget = LOOKUPVALUE('KPI'[Value],'KPI'[KPI Name],'KPI'[KPI Name],'KPI'[Year],'KPI'[Year],'KPI'[Month],'KPI'[Month],'KPI'[ACT/TARGET],"TARGET")
return
SWITCH('KPI'[Green],"Lower",IF('KPI'[Value]<CurrentTarget,1,0),"Higher",IF('KPI'[Value]>CurrentTarget,1,0))

Now you can create a matrix and apply conditional formatting on Tag column.

You can apply a slicer on Tag column to filter Red or Green.

Regards,