Forum Discussion
Conditional Formatting on a matrix grouped by Column
I have a matrix as below screenshot
Rows contain 'StockClassMasterD', 'StockClassD'
Values contain 'Sales Total', 'AVG Price'
Columns are grouped by 'Year'
I want trend indicator icons to show if the Sales total is up or down based on the previous year
As you can see, the icons i have are not correct
I want to introduce a rule to work out which icon to display, either red arrow for a decrease or green arrow for an increase
I assume I need to create a new measure comparing the current year value for the 'StockClassD' against he previous year value for 'StockClassD'
Not sure how to achive this as I have 4 years in my dataset
Use the following logic below, but keep it mind that you need a proper Date dimension table and make "mark as date table" checked.
1) Base measure
Sales Total = SUM ( FactSales[SalesAmount] )2) Previous Year Sales
If you have a proper Date table related to your fact:
Sales PY = CALCULATE( [Sales Total], DATEADD( DimDate[Date], -1, YEAR ) )3) YoY change measure
Sales YoY % = VAR Prev = [Sales PY] RETURN IF( ISBLANK(Prev), BLANK(), DIVIDE([Sales Total] - Prev, Prev) )4) Icon rule measure
Use +1 / -1 to simplify icon rules:
Sales Trend Flag = VAR d = [Sales YoY Change] RETURN IF( ISBLANK(d), BLANK(), IF( d >= 0, 1, -1 ) )5) Apply conditional formatting (icons)
In the matrix:
-
Values → Sales Total
-
Conditional formatting → Icons
-
Format by: Rules
-
Based on field: Sales Trend Flag
-
Rules:
-
If value >= 1 → green up arrow
-
If value <= -1 → red down arrow
-
-
3 Replies
Use the following logic below, but keep it mind that you need a proper Date dimension table and make "mark as date table" checked.
1) Base measure
Sales Total = SUM ( FactSales[SalesAmount] )2) Previous Year Sales
If you have a proper Date table related to your fact:
Sales PY = CALCULATE( [Sales Total], DATEADD( DimDate[Date], -1, YEAR ) )3) YoY change measure
Sales YoY % = VAR Prev = [Sales PY] RETURN IF( ISBLANK(Prev), BLANK(), DIVIDE([Sales Total] - Prev, Prev) )4) Icon rule measure
Use +1 / -1 to simplify icon rules:
Sales Trend Flag = VAR d = [Sales YoY Change] RETURN IF( ISBLANK(d), BLANK(), IF( d >= 0, 1, -1 ) )5) Apply conditional formatting (icons)
In the matrix:
-
Values → Sales Total
-
Conditional formatting → Icons
-
Format by: Rules
-
Based on field: Sales Trend Flag
-
Rules:
-
If value >= 1 → green up arrow
-
If value <= -1 → red down arrow
-
-
Hi donnellyk ,
Thank you cengizhanarslan for the response provided!
Has your issue been resolved? If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.
Thank you.
