Forum Discussion

donnellyk's avatar
donnellyk
Frequent Visitor
6 months ago
Solved

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...
  • cengizhanarslan's avatar
    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