Average across multiple data points per day

[Anonymous]

Hi All,

Cant seem to work this one out so hoping someone can help! Wasnt sure how to search for it so here we are

I have the following dataset:

Day	Country	City	Store	Sales
1	Australia	Brisbane	Store 1	20
1	Australia	Melbourne	Store 1	25
1	Australia	Sydney	Store 1	35
1	Australia	Brisbane	Store 2	55
1	Australia	Melbourne	Store 2	23
1	Australia	Sydney	Store 2	45
2	Australia	Brisbane	Store 1	69
2	Australia	Melbourne	Store 1	75
2	Australia	Sydney	Store 1	47
2	Australia	Brisbane	Store 2	39
2	Australia	Melbourne	Store 2	90
2	Australia	Sydney	Store 2	22
3	Australia	Brisbane	Store 1	43
3	Australia	Melbourne	Store 1	55
3	Australia	Sydney	Store 1	63
3	Australia	Brisbane	Store 2	33
3	Australia	Melbourne	Store 2	44
3	Australia	Sydney	Store 2	65
1	USA	LA	Store 1	56
1	USA	NYC	Store 1	79
1	USA	Dallas	Store 1	67
1	USA	LA	Store 2	47
1	USA	NYC	Store 2	60
1	USA	Dallas	Store 2	51
2	USA	LA	Store 1	64
2	USA	NYC	Store 1	71
2	USA	Dallas	Store 1	20
2	USA	LA	Store 2	75
2	USA	NYC	Store 2	46
2	USA	Dallas	Store 2	47
3	USA	LA	Store 1	55
3	USA	NYC	Store 1	56
3	USA	Dallas	Store 1	32
3	USA	LA	Store 2	59
3	USA	NYC	Store 2	31
3	USA	Dallas	Store 2	53

And all im hoping to do is get the average daily sales for each city. Im sure this is easy somehow but cant seem to perfect it!

Hoping to get:

Country	City	Sales
Australia	Brisbane	43.2
Australia	Melbourne	44.6
Australia	Sydney	39.6
USA	LA	59.3
USA	NYC	57.2
USA	Dallas	45.0

Any help is much appreciated!!

[amitchandak · Accepted solution]

Refer :https://community.powerbi.com/t5/Desktop/SUM-of-AVERAGE/td-p/197013

You need to do Avg of Avg

Try this

Measure = AVERAGEX(SUMMARIZE(Sheet1,Sheet1[Day],"_avg",AVERAGE(Sheet1[Sales])),[_avg])

[Anonymous]

Thanks everyone!

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

Just use table chart and use average of sales in the value option.

Here is the result.

[amitchandak · Accepted solution]

Refer :https://community.powerbi.com/t5/Desktop/SUM-of-AVERAGE/td-p/197013

You need to do Avg of Avg

Try this

Measure = AVERAGEX(SUMMARIZE(Sheet1,Sheet1[Day],"_avg",AVERAGE(Sheet1[Sales])),[_avg])