Power-BI Scatter plot dynamic color based on Average or Median value..

[Karthik50]

Hi Community,

The excel data source is below:

The scatter chart, based on the above excel is below:

Questions:
=> For the Average Line value of 766.66, the value is got by dividing the total sales (2300) by count of months (3) - is this correct?
=> For the Median Line value of 800, how is this value got? Can you give me the Dax equivalent?
=> I want to color dots less than the average line value or less than the median line value, with one color. How can I write this measure?

[Anonymous · Accepted solution]

Hi  @Karthik50 ,

Question1：

This is correct and will add up all the values in the month divided by the deduplication. It doesn't change dynamically based on different time periods, you can create a measure if you want to find a monthly average that isn't deduplicated or change dynamically based on the slicer selection.

Use the Analytics pane in Power BI Desktop - Power BI | Microsoft Learn

Question2：

This value is the median value of adding up according to the [Month] grouping

Measure_Median =
var _table=
SUMMARIZE(ALL('Table'),'Table'[Month],"sum",SUM('Table'[Sales]))
return
MEDIANX(_table,[sum])

Question3:

Create measure:

Color_median=
var _sum=SUM('Table'[Sales])
var _table=
SUMMARIZE(ALL('Table'),'Table'[Month],"sum",SUM('Table'[Sales]))
return
SWITCH(
    TRUE(),
    _sum<=MEDIANX(_table,[sum]),"red","yellow")

Color_avg =
var _sum=SUM('Table'[Sales])
var _table=
SUMMARIZE(ALL('Table'),'Table'[Month],"sum",SUM('Table'[Sales]))
return
SWITCH(
    TRUE(),
    _sum<=AVERAGEX(_table,[sum]),"red","yellow")

Format visual – Markers – Color –fx:

Select [Color_median] or [Color_avg]

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Karthik50]

Sorry to add to an existing "accepted solution" but I had a query: Can you tell me how line below works?

 _sum<=AVERAGEX(_table,[sum]

 
The variable "_sum" is the sum of total sales. For the attached example, it would be over 2000. But in the above code, is the "Row Context" impacting the value of the variable "_sum" when it is compared against "Averagex" of the column in the table generated throough the "summarize" function? If so, what would be the value of "_Sum" as it iterates over a summarized table of three unique months?

[Karthik50]

For those who are looking for a similar solution, I used "Calculate" function to get the right value I require. More on this, in another post of mine, here.

[Karthik50]

Thanks for the detailed info. This helps!