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 have separate column for each month. (Below snip for reference)

I have unpivot the whole months column and showing the values in Matrix visual.

 

Here scenario is like, If ACT is below TGT then the value should show Red and if the ACT is >= then its Green for certain KPI for ex, consider AAA and BBB. For CCC and DDD if the ACT is <= TGT then it should be Green and if ACT > TGT then it should be Red..

 

Somehow we have created the measure and put next to the value (Combined Months) column. (Below snip for reference)

 Column green is based on KPI table, which has columns like KPI_ID, KPI_Desc, Green (Higher/Lower).. Higher means Act above the TGT, Lower means Act below the TGT (like i explained the Green and Red appearance scenario above).

 

Now where i'm struck is, we need a slicer to choose only Red KPI's i.e., wherever the Red color appear in that above image only visible rest should be blank or hide.

 

Many Thanks in Advance.. 

 

Hope you all understand my requirement.

 

 MattAllington Baskar Sean Anonymous

 

 

 

 

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,

12 Replies

  • 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

  • salmankhan_hm's avatar
    salmankhan_hm
    Frequent Visitor
    4 years ago

    Hi All,

     

    I want to do something the same as the problem discussed here but my scenario is a bit different. I am having three different columns as shown in the image and the condition has been applied to all three. Now I want to have a button or slicer which I click that should only show the red color only. 

     

     

    Thanks in advance.