Scatter Plot Average Line

[AC_DATA]

I am trying to get an accurate average line on a scatter plot with average values plotted for items by their count.

PBI takes an average of the averages which yields an inaccurate number. How can I get the average line to show the overall average of all the items in the plot, not the average of the averages of the items by category in the plot (see the picture below)?

Here is the sample data (there is a report-level filter on TYPE = "Fruit"):

UID	TYPE	CATEGORY	ITEM_VALUE
1	Fruit	Apple	2.2
2	Fruit	Apple	1.3
3	Fruit	Apple	2.9
4	Fruit	Banana	2.0
5	Fruit	Banana	1.1
6	Fruit	Cherry	4.3
7	Fruit	Cherry	1.7
8	Fruit	Cherry	5.5
9	Fruit	Cherry	2.0
10	Fruit	Dragonfruit	3.5
11	Fruit	Dragonfruit	1.2
12	Fruit	Dragonfruit	2.0
13	Fruit	Elderberry	1.7
14	Fruit	Elderberry	3.1
15	Vegetable	Potato	2.5
16	Vegetable	Potato	1.4
17	Vegetable	Potato	2.6
18	Vegetable	Potato	1.1
19	Vegetable	Broccoli	2.6
20	Vegetable	Broccoli	3.7
21	Vegetable	Broccoli	2.7
22	Vegetable	Squash	5.1
23	Vegetable	Squash	5.4
24	Vegetable	Squash	4.7
25	Vegetable	Corn	4.4
26	Vegetable	Corn	3.9
27	Vegetable	Corn	4.9
28	Vegetable	Corn	1.8
29	Vegetable	Corn	1.9
30	Vegetable	Lettuce	1.8
31	Vegetable	Lettuce	2.5

Here is the DAX for the calculated column:

Average Item Value By Category (Column) = CALCULATE(
    AVERAGE(DATA_SET[ITEM_VALUE]),
    ALLEXCEPT(DATA_SET, DATA_SET[TYPE])
)

[Anonymous]

up

still no solution around?

i face the same problem not having the option to have a weight calculation when average on group of record

this will be major PITA for my project with Powerbi if no workaround.

i need to do a lot bubble chart at group level with a weighted average (which allow to show which group perform better than the average)

[Cmcmahan · Accepted solution]

Looking at your chart, it seems odd that your X-axis is a count of UIDs.   It seems that how many UID's a certain product category has isn't a useful way to group or plot them.  However, it seems your problem resides in how simple and uncustomizable the Data Analytics lines are for scatterplots.

I've done some reasearch, and playing around with your example file, and as far as I can tell, there's no way accomplish this with a scatterplot, unless you make a custom visual. I even played around with some of the scatterplots on the marketplace, and none of them seemed to be able to accomplish this either.

My solution would be to use a different visual.  I'd set this up as a bar and line chart.  Then you can set up a measure or your average column as the line value, and set the category types as the X-Axis.

[AC_DATA]

Thank you, @Cmcmahan. That is what I was afraid of.

I will try a different visualization type before venturing into the realms of creating custom visualizations.