Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Conditional Formatting on Matrix Row level based on formula

Hi,   Need a bit of dax help from the wizards.    I'm trying to create a colour scale where the colour of a cell in a matrix table depends on the % increase/decrease from the previous value (in r...
Need Help
  • MartijnW's avatar
    MartijnW
    4 years ago

    Hi Majad,

     

    This is an interesting one that I had to play around with a little. Anyone who can optimize this solution, please do.

     

    Basically you create a measure in DAX to calculate the percentage between this month and last month first, then you base your conditional formatting on a field like so:


     

    Here is the DAX measure I made to do this:

    Change  % = 

VAR cumulativeMinusOne = CALCULATE( SUM('Table'[Value]), PARALLELPERIOD('Calendar'[Date],-1,Month), 'Calendar'[Date] <= MAX('Table'[Year-Month]))

VAR cumulativeMinusTwo = CALCULATE( SUM('Table'[Value]), PARALLELPERIOD('Calendar'[Date],-2,Month), 'Calendar'[Date] <= MAX('Table'[Year-Month]))

VAR valueLastMonth = cumulativeMinusOne - cumulativeMinusTwo
VAR valueThisMonth = SUM('Table'[Value])
VAR changePercentage = DIVIDE( valueThisMonth, valueLastMonth, BLANK() )

RETURN IF( DIVIDE( valueThisMonth, valueLastMonth, BLANK()) <> BLANK(),  DIVIDE( valueThisMonth, valueLastMonth, BLANK()) -1, BLANK() )
MartijnW's avatar
MartijnW
Frequent Visitor
4 years ago

Hi Majad,

 

This is an interesting one that I had to play around with a little. Anyone who can optimize this solution, please do.

 

Basically you create a measure in DAX to calculate the percentage between this month and last month first, then you base your conditional formatting on a field like so:


 

Here is the DAX measure I made to do this:

Change  % = 

VAR cumulativeMinusOne = CALCULATE( SUM('Table'[Value]), PARALLELPERIOD('Calendar'[Date],-1,Month), 'Calendar'[Date] <= MAX('Table'[Year-Month]))

VAR cumulativeMinusTwo = CALCULATE( SUM('Table'[Value]), PARALLELPERIOD('Calendar'[Date],-2,Month), 'Calendar'[Date] <= MAX('Table'[Year-Month]))

VAR valueLastMonth = cumulativeMinusOne - cumulativeMinusTwo
VAR valueThisMonth = SUM('Table'[Value])
VAR changePercentage = DIVIDE( valueThisMonth, valueLastMonth, BLANK() )

RETURN IF( DIVIDE( valueThisMonth, valueLastMonth, BLANK()) <> BLANK(),  DIVIDE( valueThisMonth, valueLastMonth, BLANK()) -1, BLANK() )
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi, thanks for the comprehensive response! I feel like this could work...just need a bit of help with the fields

 

i have...

 

table[value]

calendar[date] - from date dimension

 

however i dont have year-month in the table, only in the date dimension. I only have a date field in the table. Will we need to adjust?

 

edit: ok i created year-month in the table however i got the following response when creating the conditional formatting

 

 

  • MartijnW's avatar
    MartijnW
    Frequent Visitor
    4 years ago

    Hi Majad,

    You need to make sure that the year-month column is of type Date.

     

    Make a connection between this year-month column and calendar[date].

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      this worked, thank you, thank you, thank you!

      • MartijnW's avatar
        MartijnW
        Frequent Visitor
        4 years ago

        Hi Majad,

         

        Glad I could help!

         

        Martijn