Forum Discussion
Want to apply Conditional formatting on matrix visual
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.
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.
Hi danextian , Thank you for providing this solution is it possible to apply one more conditional formatting on same MOM conditional formatting like if my %types of values >95% make it as dark green. how can I achieve both conditional formatting on this matrix visual simultaneouly.
Also I have also another requirement in this above matrix I am using value column as numeric but is it possible to use Values column as text column in matrix visual. Value will come but can I apply same conditional formatting without using dynamic formatting in here as well. I have attached a pbix file with use of text value sheet name as Duplicate of Duplicate of Page 1. I want same MOM formatting like above if current month greater to prev return light green else red.
Folder - https://drive.google.com/drive/folders/1Di3lz238sVc9ZcIba0HEH4WuDxA31W7U?usp=sharing
Pbix link - https://drive.google.com/file/d/1ud835pnHlWV5E6NxInbDK7ady7vQGzCt/view?usp=sharing
File Link - https://docs.google.com/spreadsheets/d/1zHDEoM1fGvprU6567qwq_-I0AoHPnjza/edit?usp=sharing&ouid=111390216161662712209&rtpof=true&sd=true