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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

nandukrishnavs

Conditional formatting in the scatter chart based on a trend line value

How do we conditionally format bubbles in the Power BI scatter chart based on the trend line value?

First we have to create a DAX measure to get the trend line values. Since February 2023 Power BI update, we have two new statistical functions LINEST() and LINESTX(). Using these functions we can easily create DAX measure to get the trend line.

 

Sample data

nandukrishnavs_0-1678008644250.png

 

Below is the trend line measure 

 

 

 

TrendLine =
VAR _linest_output =
    LINESTX (
        ALLSELECTED ( 'Table' ),
        'Table'[Variable 2],
        'Table'[Variable 1],
        TRUE
    )
VAR _slope =
    MAXX ( _linest_output, [Slope1] )
VAR _intercept =
    MAXX ( _linest_output, [Intercept] )
VAR _result =
    SUMX (
        DISTINCT ( 'Table'[Variable 1] ),
        _intercept + _slope * 'Table'[Variable 1]
    )
RETURN
    _result

 

 

 

 

 You may notice an error indication in the variables _slope and _intercept. 

nandukrishnavs_0-1678008930424.png

But you can ignore it. Hope it will be resolved in the upcoming Power BI update. Anyway, the measure will work as expected. 

Next, we need to create a measure for conditional formatting.

 

Below is the sample measure. 

 

 

 

Color =
IF ( SUM ( 'Table'[Variable 2] ) > [TrendLine], "Red", "Green" )

 

 

 

 

You can customize the logic based on your need. Here we are just coloring the bubble as red if the Y axis value is higher than trend line, else color will be green. 

 

Now you can use scatter chart from the visualization pane. In this example, you can use [Variable 1] column into X axis, [Variable 2] into Y axis and [Category] column into Values.

If you use Legend property, conditional formatting will not work.

 

Next you can navigate to visual formating pane. Under Markers option, you can click on fx icon near to color section.

 

nandukrishnavs_1-1678009669028.png

 

Then you can select Format style as Field value, select newly created measure (Color) in What field should we base this on? and click ok.

nandukrishnavs_2-1678009865161.png

Then you will get the result as below. 

nandukrishnavs_3-1678010113918.png

 

To enable the trend line in the scatter chart, you can navigate into analytics pane and turn on trend line.

nandukrishnavs_4-1678010317135.png

 

Hope this blog is helpful 😊. Let me know your suggestions/feedbacks. 

 

Regards,

Nandu Krishna

 

Comments

very helpful @nandukrishnavs 

Thanks Nandu really good one.

This is really helpful.. Thanks Nandu!!

I have another question ? Is there way that we can control the opacity of scatter plot so that overlapping data points are more visible?

@Manmeet Opacity can be controlled by using HEX value.

 

Calculated Table

Intensity = GENERATESERIES(0, 100, 5)

Measure

Intensity Value = SELECTEDVALUE('Intensity'[Value], 100)

Measure

Color = 
VAR _defaultColor = "#1565C0"
VAR _transparencyMapping =
    DATATABLE (
        "Perc", INTEGER,
        "Sufix", STRING,
        {
            { 100, "FF" },
            { 95, "F2" },
            { 90, "E6" },
            { 85, "D9" },
            { 80, "CC" },
            { 75, "BF" },
            { 70, "B3" },
            { 65, "A6" },
            { 60, "99" },
            { 55, "8C" },
            { 50, "80" },
            { 45, "73" },
            { 40, "66" },
            { 35, "59" },
            { 30, "4D" },
            { 25, "40" },
            { 20, "33" },
            { 15, "26" },
            { 10, "1A" },
            { 5, "0D" },
            { 0, "00" }
        }
    )
VAR _color =
    CONCATENATEX (
        FILTER ( _transparencyMapping, [Perc] = [Intensity Value] ),
        _defaultColor & [Sufix]
    )
RETURN
    _color

nandukrishnavs_0-1681811475464.png

 

 

Nice job!