Forum Discussion

Manas0396's avatar
Manas0396
Frequent Visitor
7 months ago
Solved

Want to apply Conditional formatting on matrix visual

Hi I want to perform a month over month conditional formatting on this matrix visual. I don't have proper date column as well as date table I want to apply conditional formatting only this %types of ...
Need Help
  • danextian's avatar
    danextian
    7 months ago

    Hi Manas0396 

    Move your dynamic format string to a measure so you can re-use it:

    Dynamic Format String = 
SWITCH (
    SELECTEDVALUE ( Sheet1[KPI] ),
    "No. of hero skus", "0",
    "Listing Share", "0" & """%""",
    "Availability (Hero SKU)", "0" & """%""",
    "Content Execution on PDP", "0" & """%""",
    "Rating And Reviews", "0" & """%""",
    "In search", "0" & """%""",
    "On category page", "0" & """%""",
    "Impression Share (only for specific formats).", "0" & """%""",
    "Revenue (EUR)", "#,.00k",
    "Spent (EUR)", "#,.00k",
    "ROAS", "0.00"
)

    Create these measures:

    Previous Month Total Value = 
CALCULATE (
    [Total Value],
    FILTER (
        ALL ( Sheet1[Date], Sheet1[Month_Year] ),
        VALUE ( Sheet1[Date] ) = VALUE ( MAX ( Sheet1[Date] ) ) - 1
    )
)
--NOTE: I had to use VALUE because [Date] as YYYYMM is not numeric

Variance = 
[Total Value] - [Previous Month Total Value]


Text Color = 
IF (
    CONTAINSSTRING ( [Dynamic Format String], "%" ),
    IF ( [Variance] > 0, "green", "red" )
)

    Your current setup isn't the best practice. If it were me I'd use a dedicated dates and create a real date column in the fact table. 

MFelix's avatar
MFelix
Super User
7 months ago

Hi Manas0396 ,

 

Based on your configuration your best option is to do a visual calculation.

 

conditional = 

if (  [KPI] in   {"Listing Share", "Availability (Hero SKU)",  "Content Execution on PDP",   "Rating And Reviews", 
   "In search", 
    "On category page",
    "Impression Share (only for specific formats)."}  ,  
          
           IF(next([Retailer_id], COLUMNS) = [Retailer_id], IF(

          
          NEXT([Total Value]) >= [Total Value],1, 0)))

 

Now do the formatting based on this value

 



Please see file attach.

 

Manas0396's avatar
Manas0396
Frequent Visitor
7 months ago

Hi Mfelix, Thank you for providing the solution under A --> West --> In search values are decreasing but still showing as green color.

  • MFelix's avatar
    MFelix
    Super User
    7 months ago

    Hi Manas0396 ,

     

    I had the syntax in the wrong order this should be:

    conditional = 

if (  [KPI] in   {"Listing Share", "Availability (Hero SKU)",  "Content Execution on PDP",   "Rating And Reviews", 
   "In search", 
    "On category page",
    "Impression Share (only for specific formats)."}  ,  
          
           IF(next([Retailer_id], COLUMNS) = [Retailer_id], IF(

          
          [Total Value] >=NEXT( [Total Value]),1, 0)))



    Now it should return correct value.