Average Calculation

[UK_User123456]

Hi All,

I have the below data, im just simply trying to do an average calulcation for each of the ID's that I have, normally I would have just put the "Amount" into the values field and used the average function, but it does not give me the average function to select, is there anothger way of doing this?

ID	Amount
1	12
1	10
1	3
1	5
1	6
2	20
2	27
2	32
3	15
4	16
5	17
6	3
6	45

my outcome would be:

ID	Amount
1	7.2
2	26.33333
3	15
4	16
5	17
6	24

[cosminc]

Hi

the column Amount is number type and Modelling - Default summarization is Sum?

Hope it helps,

Cosmin

[KHorseman · Accepted solution]

@cosminc yeah. You can use the little down arrow there to change it to any other summarization type, such as average. Or of course write an explicit measure that does the same thing. Such as...

Avg Amount = AVERAGE(TableName[Amount])

[UK_User123456]

Hi All,

Thanks for the response, it just clicked to me after reading your responses that my amount for some reason when imported into power bi it changed the format to text when it should have been whole number which is the reason I couldnt summarise it.

Thanks

[KHorseman]

This is fairly easy in the Query Editor. Go to the Transform tab and use the Group By button. Group by your ID column and use sum of Amount as the aggregation type.

That assumes you want a hard table with this aggregation pre-calculated. You can also do this in a report by either writing a measure that does an average of Amount, and just set that measure next to the ID column on your report page, or just drop the two columns side by side and set the aggregation on Amount to average, and ID to "do not summarize."