Conditional Formatting on a matrix grouped by Column

[donnellyk]

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

[v-tejrama]

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.

[v-tejrama]

Hi @donnellyk ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[cengizhanarslan · Accepted solution]

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