Forum Discussion
donnellyk
6 months agoFrequent Visitor
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 indic...
- 6 months ago
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
-
-
v-tejrama
6 months agoCommunity Support
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.