Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Majad_Chowdhury
Advocate II
Advocate II

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!

1 ACCEPTED SOLUTION
MartijnW
Frequent Visitor

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:


conditional formatting previous month.png

 

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

View solution in original post

6 REPLIES 6
MartijnW
Frequent Visitor

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

MartijnW
Frequent Visitor

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:


conditional formatting previous month.png

 

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

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

 

Majad_Chowdhury_0-1660126636292.png

 

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

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

Hi Majad,

 

Glad I could help!

 

Martijn

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.