Line Chart Conditional Formatting

I’m really pleased and excited to introduce you to Daniil Maslyuk (Twitter: @DMaslyuk).  I first met Daniil about a year ago (originally via email) as he was preparing to move to Australia.  Daniil made the plunge shortly there after and migrated to Australia (from Russia) – he now works as a Power BI consultant for Agile BI here in Sydney.  Daniil and I have worked together on a couple of occasions when he helped me deliver live training to a couple of larger groups that were too big for me to deliver on my own.  Daniil knows an awful lot about DAX and Power BI and I am thrilled that he has agreed to share some of his knowledge here on my blog.

Over to Daniil.

Line Chart Conditional Formatting

Have you ever wished you could change the line colour depending on the overall trend? For example, if your sales increase over time, the line is green; if there is a decline, then the line is red. While this functionality is not yet natively available in Power BI Desktop, it does not mean this cannot be done! In this article, I am going to show you how to achieve this effect.

Sample data

If you want to follow along with the examples in this article, you can download the sample dataset I am using: Trend Color – Start.pbix

This is a rather simplistic dataset:

Steps to follow:

1. Create a measure that you are going to track over time. In our case, I created a measure that smoothes sales:

   Total Sales = 
   CALCULATE (
       SUM ( Sale[SalesAmount] ),
       DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -1, MONTH )
   )

   Matt has described the technique of smoothing your data in his “Trending and Smoothing” article previously. I am using a different flavour of the same technique: in my formula, DATESINPERIOD returns a table of dates for a month preceding the last date in the current filter context. In other words, I am calculating SalesAmount for one rolling month (not necessarily calendar month).
   Note that in our case smoothing your data is not necessary — I am only doing this so that the graphs look less erratic 🙂

   At this stage, we can build the following report:
   

2. Create a measure that calculates the slope of the line. What is slope? For our purposes, this metric tells you whether the overall trend is positive or negative. If slope is greater than zero, then the trend is positive. I’ve written a blog post on simple linear regression in DAX, and you can copy and paste the formula from there.
   If you find it useful, please give it kudos in Quick Measures Gallery: Simple Linear Regression
   The good news is that you don’t need to understand how this formula works to use it for your benefit 🙂
   Here is the formula that I am using in our example:

   Slope = 
   VAR Known =
       FILTER (
           SELECTCOLUMNS (
               ALLSELECTED ( 'Date' ),
               "Known[X]", 'Date'[Date],
               "Known[Y]", [Total Sales]
           ),
           AND (
               NOT ( ISBLANK ( Known[X] ) ),
               NOT ( ISBLANK ( Known[Y] ) )
           )
       )
   VAR Count_Items =
       COUNTROWS ( Known )
   VAR Sum_X =
       SUMX ( Known, Known[X] )
   VAR Sum_X2 =
       SUMX ( Known, Known[X] ^ 2 )
   VAR Sum_Y =
       SUMX ( Known, Known[Y] )
   VAR Sum_XY =
       SUMX ( Known, Known[X] * Known[Y] )
   VAR Average_X =
       AVERAGEX ( Known, Known[X] )
   VAR Average_Y =
       AVERAGEX ( Known, Known[Y] )
   RETURN
       DIVIDE (
           Count_Items * Sum_XY - Sum_X * Sum_Y,
           Count_Items * Sum_X2 - Sum_X ^ 2
       )

3. Add a trend line to the graph. To do so, select the line chart, then click Analytics -> Trend Line -> Add:
   
   We are adding this line to confirm visually whether sales increase or decrease over time. At this point, your graph should look as follows:
   
4. Create the Trend table. You can click Get Data -> Blank Query, then rename the query to Trend. Open Advanced Editor and replace the code there with the following:

   #table (
       type table [Trend = text],
       {
           {"Increase"},
           {"Decrease"}
       }
   )

   

   I’ve created this table using the Query Editor (Power Query).  As an alternative you could also use the Enter Data feature in Power BI.

5. Create the Color Sales measure. For now, it should simply be

   Color Sales = [Total Sales]

   Stop here before proceeding. The next step is the one where all the magic happens. The key point below is that a legend in a chart provides filter context, just like a row or column in a matrix.

6. Use the Trend column from the Trend table as legend in our line chart and pick the colours: green for Increase, red for Decrease. In the Values field well, replace the Total Sales measure with Color Sales.
7. Replace the Color Sales measure formula. I used the following code:

   Color Sales =
   VAR TrendValue =
       SELECTEDVALUE ( 'Trend'[Trend] )
   RETURN
       SWITCH (
           TRUE (),
           AND ( TrendValue = "Decrease", [Slope] < 0 ), [Total Sales],
           AND ( TrendValue = "Increase", [Slope] >= 0 ), [Total Sales]
       )

   What does this measure do? At first, it may look puzzling because it returns [Total Sales] for either of the conditions in SWITCH, but if you use this measure in our line chart, you will notice that it uses only one Trend category at a time: either Increase or Decrease, depending on the value of Slope.
   Why do we need to create the simple version of Color Sales first? That’s because if we create the final version right away, we will have to hunt down those periods where sales increase and also where they decrease, because with the final version, we see only one Trend category — not two. Starting with the simple version makes it easier to pick the colors.

8. Optional: create the Trend Card measure:

   Trend Card =
   IF (
       [Slope] < 0,
       "Decrease" & UNICHAR ( 10060 ),
       "Increase" & UNICHAR ( 9989 )
   )

   This measure returns a green tick (✅) or a red cross (❌) when the trend is positive or negative, respectively.

Here is our final result:

Post any comments or questions you may have in the comments section below.

Download the final report: Trend Color – Finish.pbix