Forum Discussion
Conditional Formatting on a matrix grouped by Column
- 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
-
-
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
-