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,

 

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