Forum Discussion

oliverblane's avatar
oliverblane
Icon for Helper III rankHelper III
3 years ago

Conditional Formatting of Area Chart

Hi there!

 

I am looking to conditionally format the colour of an area chart based on whether a KPI is being met, similar to how it is done here: https://exceleratorbi.com.au/line-chart-conditional-formatting/. My KPI is Registrations, and I would like my area chart to appear red if Registrations in last 30 days < Registrations in the previous 30 day window (i.e. where the date is more than 30 days ago but less than 60 days ago), or green otherwise.

 

My two Registration measures look like

VC Registrations in Last 30 = 
VAR MaxDate = MAX('VC'[created date])
RETURN CALCULATE(
    COUNTROWS('VC),
    FILTER(
        ALLSELECTED('VC'),
        'VC'[created date] >= MaxDate - 30
    )
)

and

VC Registrations in Previous 30 = 
VAR MaxDate = MAX('VC'[created date])
RETURN CALCULATE(
    COUNTROWS('VC'),
    FILTER(
        ALLSELECTED('VC'),
        AND('VC'[created date] >= MaxDate - 60, 'VC'[created date] <= MaxDate - 30)
    )
)
 

I have then created a legend table with a row value for each scenario:

Trend
Increase
Decrease

 

I have finally created a switch measure to use on the area plot:

Color Cumulative VC Registrations = 
VAR TrendValue =
    SELECTEDVALUE('Trend'[Trend])
VAR Last30 = CALCULATE([VC Registrations in Last 30])
VAR Previous30 = CALCULATE([VC Registrations in Previous 30])
RETURN
    SWITCH(
        TRUE(),
        AND(TrendValue = "Decrease", Last30 < Previous30), [Cumulative VC Registrations],
        AND(TrendValue = "Increase", Last30 >= Previous30), [Cumulative VC Registrations]
    )

where Cumulative VC Registrations is a cumulative sum of registrations.

 

However, putting this all together, I am not getting the desired output. For example, in the selected date range shown below, the KPI is red, which is correct, however the graph still remains green (I have set Red for when Trend=Decrease and Green for when Trend=Increase), but with a red dot.


In a rare occasion, the graph displays with a small chunk of red area:

If the KPI shows an increase, the graph shows completely green as expected. I feel like I am close to the correct solution.

 

Thanks for any help on this, and please let me know if an example .pbix file is required.

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi oliverblane ,

    In order to better understand your demands and give the right solution, could you please provide some more specific information? such as your desensitized example data and a screenshot of your desired results?

    Thanks for your efforts & time in advance.

     

    Best regards,
    Community Support Team_Binbin Yu

     

    • oliverblane's avatar
      oliverblane
      Icon for Helper III rankHelper III
      3 years ago

      Hi Anonymous, thank you for your reply. Here is a link to a sample version of the mentioned report with desensisitized data: Example_Dashboard.pbix

       

      The desired results are as follows. When an end date is selected on the date slicer, two measures are calculated:

      M1) Number of registrations in the 30 day window leading up to the end date

      M2) Number of registrations in the previous 30 day window (any registrations between 60 days and 30 days before the end date).

       

      The KPI visual currently shows red if M1 < M2, and green if M1 > M2, as expected. I would also like the cumulative area chart to change colour too, like this:

      and

      Currently, it works when M1 > M2, but when M1 < M2, the following shows:

      Thanks again for your help on this!