Forum Discussion
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 values in this matrix. In this matrix I have 3rows(Section, Funnel, KPI) , 3columns(Country, Retailer_id, month_year) all are string and the values are decimal I have done dynamic formatting to bring a one format. My requirement is apply conditional formatting on %types of value in this matrix if current month value greater or equal light green else red. I have attached sample pbix and excel file. I have tried it multiple ways but not achieve it.Can you please help me in this scenario
folder link - https://drive.google.com/drive/folders/1YljfPUOh5FSkXptwmoXgj0EoKsPXrBn3?usp=sharing
Excel link - https://docs.google.com/spreadsheets/d/1D7DtQtqF_AkJgMi6OLAbr8s8GQIgFuna/edit?usp=sharing&ouid=111390216161662712209&rtpof=true&sd=true
Pbix link - https://drive.google.com/file/d/1Xcvyl49RUZ5zpjFJAkVqi71OaDvZeOud/view?usp=sharing
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.
6 Replies
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
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.Hi Mfelix, Thank you for providing the solution under A --> West --> In search values are decreasing but still showing as green color.
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.
