Forum Discussion
histogram problem
I'm trying to create a histogram using this data. I'm using Activity Score as the Value and want to show the average of Average Test Score in each bucket. What I'm getting though is Frequency of Activity Score in each bucket. So for the first bucket (Activity Scores 4.8-13.86) I get a frequency of 4 (4.8 twice, 9.7, 12.6 and 13.5). I would like an average of .8333, .8, .7666, .775 and .6222. Any ideas what I'm doing wrong? Thank you in advance.
| ACTIVITY_SCORE | Average of Test Score |
| 4.8 | 0.833333333 |
| 4.8 | 0.8 |
| 9.7 | 0.766666667 |
| 12.6 | 0.775 |
| 13.5 | 0.622222222 |
| 15.5 | 0.95 |
| 15.5 | 0.766666667 |
| 16.4 | 0.633333333 |
| 17.4 | 0.822222222 |
| 17.4 | 0.766666667 |
| 19.3 | 0.922222222 |
| 19.3 | 0.625 |
| 19.3 | 0.444444444 |
| 21.3 | 0.777777778 |
| 22.2 | 0.905555556 |
| 22.2 | 0.455555556 |
| 23.2 | 0.8 |
| 23.2 | 0.733333333 |
| 23.2 | 0.688888889 |
| 23.2 | 0.45 |
| 23.2 | 0.811111111 |
| 24.2 | 0.9 |
| 24.2 | 0.641666667 |
| 25.1 | 0.858333333 |
| 25.1 | 0.798333333 |
| 25.1 | 0.858333333 |
| 25.1 | 0.855555556 |
| 25.1 | 0.788888889 |
Accordin to your description, you want to create average for each bucket of your table. Right?
In this scenario, you should create a bucket table like:
Then create a Bucket Column in your source table.
Bucket = CALCULATE(VALUES(Table1[BucketGroup]),FILTER(Table1,Table2[Score]>Table1[MinScore] && Table2[Score]<=Table1[MaxScore]))
Then you can just include this column and Activity Score column into same table. Change the aggregation type into "Average".
Regards,
That looks perfect. I'll give it a try. Thank you.
3 Replies
Accordin to your description, you want to create average for each bucket of your table. Right?
In this scenario, you should create a bucket table like:
Then create a Bucket Column in your source table.
Bucket = CALCULATE(VALUES(Table1[BucketGroup]),FILTER(Table1,Table2[Score]>Table1[MinScore] && Table2[Score]<=Table1[MaxScore]))
Then you can just include this column and Activity Score column into same table. Change the aggregation type into "Average".
Regards,