Forum Discussion

weezle80's avatar
weezle80
Frequent Visitor
3 years ago
Solved

Conditional Formatting Based on Month-Over-Month Matrix Values

I am completely lost trying to figure out how to conditionally format my matrix columns so it calls out values that are less than the previous month. From my AgencyAssignment table, I have the Contract Name field under Rows, Month (Date Hierarchy) under Columns, and Sum of Agencies under Values. Contract A had 2 Agencies for Jan, Feb, Mar, & April, for a total of 8. However, Contract B dropped from 65 Agencies in January to 63 Agencies in February, so that 63 needs to be highlighted. Same for the drops for Contract D, and so on. I have been digging through forums and YouTube and have tried multiple things and nothing is working out right. Can you help?

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi weezle80 ,

    I have created a simple sample, please reer to my pbix file to see if it helps you.

    Create 2 measures.

    Measure =
VAR _1 =
    EDATE ( MAX ( 'Table'[DATE] ), -1 )
VAR _2 =
    CALCULATE (
        MAX ( 'Table'[value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[DATE] = _1
                && 'Table'[contract name] = SELECTEDVALUE ( 'Table'[contract name] )
        )
    )
VAR _mindae =
    MINX ( ALL ( 'Table' ), 'Table'[DATE] )
RETURN
    IF ( MAX ( 'Table'[DATE] ) = _mindae, 0, MAX ( 'Table'[value] ) - _2 )

    Measure 2 = IF([Measure]<0,"Red",BLANK())

     

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi weezle80 ,

    I have created a simple sample, please reer to my pbix file to see if it helps you.

    Create 2 measures.

    Measure =
VAR _1 =
    EDATE ( MAX ( 'Table'[DATE] ), -1 )
VAR _2 =
    CALCULATE (
        MAX ( 'Table'[value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[DATE] = _1
                && 'Table'[contract name] = SELECTEDVALUE ( 'Table'[contract name] )
        )
    )
VAR _mindae =
    MINX ( ALL ( 'Table' ), 'Table'[DATE] )
RETURN
    IF ( MAX ( 'Table'[DATE] ) = _mindae, 0, MAX ( 'Table'[value] ) - _2 )

    Measure 2 = IF([Measure]<0,"Red",BLANK())

     

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.