histogram problem

[DHB]

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

[v-sihou-msft · Accepted solution]

@DHB

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,

[v-sihou-msft · Accepted solution]

@DHB

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,

[DHB · Accepted solution]

That looks perfect.  I'll give it a try.  Thank you.

[DHB]

I've used the information that you gave and been able to do exactly what I was trying to do.  Thank you so much.