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 row).

Please note this is in Customer > Database level

 

So in the below table, Customer 1, DB1 would show a 200% increase from 2022-05 (10) to 2022-06 (30)

 

customerdatabase2022-052022-062022-07
Customer 1DB1103020
 DB23050100
Customer 2DB15155
 DB2101010

 

The colour scale should be something along the lines of:

<-25% decrease : Colour 1

>-25% - 0% : Colour 2

>0%: Colour 3

 

Hope thats enough to make sense!

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() )

6 Replies

  • 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].

  • MartijnW's avatar
    MartijnW
    Frequent Visitor
    4 years ago

    Hi all,

     

    As I suspected, the above example is far more complex than it needs to be. Please see this link for the usage of the function DATEADD(). Far more comprehensive.

     

    https://dax.guide/dateadd/

     

    You can quite easily replace the first three variables with this one function, that is meant for exactly this purpose.

     

    Martijn