Forum Discussion

mpraka's avatar
mpraka
Advocate II
11 months ago
Solved

Background color for Matrix visual for Average values

Hi All,

 

I am trying to give background color in Matrix visuals based on Average Values.
As you see in the attached screenshot I have "Sites" column in Filter.
I have Category, Metrics column followed by their respective values for each month.
Difference metrics will have different color thresholds.

 

I would like to write a formula in Dax which achieves the following:
Metrics A should show its relevant BG colors - if it is greater than or equal to 95 then Green BG
if is less than 90 then red else Yellow

Metrics B should show its relevant BG - if it is greater than or equal to 90 then Green BG
if is less than 85 then red else Yellow

The main thing is if we do multi selection in Filter, i.e., if we select both the sites in sites filter then the matrix visuals will show average values and BG color has to be changed accordingly with respect to the "Average"Values.
What would be the DAX?

Thank you in Advance.

 

Regards,

Prakash M

Need Help
  • AmiraBedh's avatar
    AmiraBedh
    11 months ago

    Hello !

    Thank you for posting on Fabric community.

    You can use your existing KPI measure in place of value if it has a different name 

    Avg Value (across Sites) =
AVERAGEX(
    VALUES('Sites'[Site]),
    CALCULATE([Value])        
)

    Then you switch the background color depending on the thresholds :

    BG Color :=
VAR m = SELECTEDVALUE('Table'[Metrics])
VAR v = [Avg Value (across Sites)]        
RETURN
SWITCH(
    TRUE(),
    m = "Metrics A" && v >= 0.95, "#118D57",   -- green
    m = "Metrics A" && v <  0.90, "#D14343",   -- red
    m = "Metrics B" && v >= 0.90, "#118D57",   -- green
    m = "Metrics B" && v <  0.85, "#D14343",   -- red
    "#F7B500"                                  -- yellow 
)

4 Replies

  • AmiraBedh's avatar
    AmiraBedh
    Super User
    11 months ago

    Hello !

    Thank you for posting on Fabric community.

    You can use your existing KPI measure in place of value if it has a different name 

    Avg Value (across Sites) =
AVERAGEX(
    VALUES('Sites'[Site]),
    CALCULATE([Value])        
)

    Then you switch the background color depending on the thresholds :

    BG Color :=
VAR m = SELECTEDVALUE('Table'[Metrics])
VAR v = [Avg Value (across Sites)]        
RETURN
SWITCH(
    TRUE(),
    m = "Metrics A" && v >= 0.95, "#118D57",   -- green
    m = "Metrics A" && v <  0.90, "#D14343",   -- red
    m = "Metrics B" && v >= 0.90, "#118D57",   -- green
    m = "Metrics B" && v <  0.85, "#D14343",   -- red
    "#F7B500"                                  -- yellow 
)
  • srlabhe's avatar
    srlabhe
    Super User
    11 months ago

    Hi Create a measure like below

    var_MetricsA= if (MetricsA>=95,0,

    if(MetricsA<=90,1,2))

    Then use this varMetricsA in conditional formatting with Rules to setup on varMetricsA and add the conditions there like

    if varMetrics=0 then select any Green color from pallet

    if varMetrics=1 then select any red color from pallet

    if varMetrics>1 then yellow

    Hope it helps

    • mpraka's avatar
      mpraka
      Advocate II
      11 months ago

      Thank you Anonymous , AmiraBedh  and srlabhe  for your response it really worked.

      But also I have some cells with blank values where I have to give Grey color.
      If I try to give like this it is not working, how to make isblank function work in it?
      m = "Metrics B" && isblank(v) then Grey

      Regards,

      Prakash

  • Anonymous's avatar
    Anonymous
    Not applicable
    11 months ago

    Hi mpraka ,

    Thanks AmiraBedh  for jumping in here. The solution you shared with Avg Value (across Sites) and BG Color will work to achieve the requirement. mpraka did you get a chance to try it out yet? If not, happy to help you implement it.

    Thanks,
    Akhil.