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,

Thiyagu's avatar
Thiyagu
Helper III
9 years ago

Hi v-sihou-msft,

 

Could you also help me on the below issue i have faced, actually i have 13 columns (12months  and 1 comment).

 

 

So when i unpivot all columns (13) it was done because i dont have any values in comment column (Only Null). Now when i try to include some text and try the same, it pops the below error

Final stage i'm struck. 

 

Many Thanks

 

 

 

 

MalS's avatar
MalS
Resolver III
9 years ago

I haven't gone through the entire thread, but...do you need to unpivot the Comment column? Could you just unpivot the months only? 

  • Thiyagu's avatar
    Thiyagu
    Helper III
    9 years ago

    Hi MalS,

     

    Actually i want to show the data in matrix visual, please check the above images. I did unpivot all months with comment column as well and it works because that time comment column had null values. after i put some testing data and refresh it i faced the datatype conversion error.. So i need to show the comment part after all months like below.

     

    Jan Feb Mar.... Dec.. Comments

     

    Hope you understand my requirement. if you want i ll give further explanation.

     

    Thanks,

    THiyaga

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