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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
oliverblane
Helper III
Helper III

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.

oliverblane_1-1670853609363.png


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

oliverblane_0-1670853592984.png

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.

2 REPLIES 2
v-binbinyu-msft
Community Support
Community Support

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

 

Hi @v-binbinyu-msft, 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:

oliverblane_0-1670936267151.png

and

oliverblane_1-1670936319545.png

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

oliverblane_2-1670936366102.png

Thanks again for your help on this!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.