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,

v-sihou-msft's avatar
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,

  • Thiyagu's avatar
    Thiyagu
    Helper III
    9 years ago

    Hi v-sihou-msft,

     

    Really very happy to see the response for this query, 

    I have did all the steps you have mentioned, but i get the below error while creating calc column

     

     

     

     

    "A table of multiple values was supplied where a single value was expected." 

     

    Am i did anything wrong here?

     

     

  • Thiyagu's avatar
    Thiyagu
    Helper III
    9 years ago

    Hi v-sihou-msft,

     

    Yeah i have found why the error appears, it retrieves lot of records with same names, so i have added other two columns in lookup now it works fine.

     

    But i need to show the Target row as blank, so i have tried following measure, but it gives me an error, could you sort it out.

     

    Many Thanks,

    Cond_Form = 
Var _Target = LOOKUPVALUE(Input[Value],Input[KPI],Input[KPI],Input[YEAR],Input[YEAR],Input[Months],Input[Months],Input[Category],Input[Category],Input[BG Name],Input[BG Name],Input[Site],Input[Site],Input[ACT/TARGET],"TGT")
Var _TGT = VALUES(Input[ACT/TARGET])
Return 
	IF(_TGT = "TGT",BLANK(),
		SWITCH(Input[Green],"Lower",IF(Input[Value] <= _Target,1,0),"Higher",IF(Input[Value]>=_Target,1,0)))
  • Thiyagu's avatar
    Thiyagu
    Helper III
    9 years ago

    Hi v-sihou-msft,

     

    Thank you for timely help and it works what i expected... Really Great and Many thanks

  • 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