Forum Discussion

Thiyagu's avatar
Thiyagu
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's avatar
    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,

MalS's avatar
MalS
Resolver III
9 years ago

Yes, but you could just unpivot the months (not the Comment column). 

 

Then when you create the matrix, you can add the Comment field to the rows section of the matrix. 

 

 

 

MalS's avatar
MalS
Resolver III
9 years ago

Alternatively, you could unpivot the Comments column with the months, then make sure the resulting Values column had a data type of Text. Then, instead of summarizing the Value field using sum, min , max (or whatever you used originally), you should be able to summarize it using 'First' (which will work on a text field).