Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]) )
Solved! Go to 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.
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)
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |