How to create a table with average weights by group

[timflo]

Hi all, 

I'm trying to create a table in Power BI that I have created manually already. Here is the situation:

I have 3 adresses where we have 1 or multiple boxes to collect something. We collect every 2 weeks. For Example

City	Date	Device ID	Adress	Weight
Amsterdam	23/11/2021	111	Johnson street 2	50
Amsterdam	23/11/2021	112	Johnson street 2	60
Amsterdam	23/11/2021	113	Johnson street 2	70
Amsterdam	23/11/2021	114	Williamson lane 10	55
Amsterdam	23/11/2021	115	Rogers road 4	65
Amsterdam	07/12/2021	111	Johnson street 2	80
Amsterdam	07/12/2021	112	Johnson street 2	75
Amsterdam	07/12/2021	113	Johnson street 2	90
Amsterdam	07/12/2021	114	Williamson lane 10	65
Amsterdam	07/12/2021	115	Rogers road 4	50

Here is the problem: I need to know the total average weight per adress over time. So for example Johnson street's total weight the first time is 180 and the second time 245 giving an average of 180 + 245 / 2 = 212,50 per collection. Power BI however gives me the average value of the independent rows (so avg 60 for the first pickup and avg 81,6 for the second pickup. 

Is there a way to add up the weights before calculating the average and this way get a total average per adres per pickup? 

Thank you very much!

[amitchandak · Accepted solution]

@timflo , Try a measure like

AverageX(summarize(Table, Table[Adress], Table[Date], "_1", Sum(Table[Weight])),[_1])

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new table.

[tamerj1]

@timflo 

Yeat this doable. But how does you report looks like?  I guess you want to aggregate the results by adress? Or you also intersted of having moving average based on dates?

[timflo]

Hi Tamerj, 

By report, do you mean the powerBI visuals or the raw data. The latter is an excel file with data bi-weekly data entries with the following colums:

City

Date

Weekday

Weeknumber

Month

Year

Location ID (= device ID)

Adress

Zip code

Province

Country

Location (= CONCATENATE)

Audience

Net weight

Polluted (yes/no)

Right now we're handling 1 city but we're going to expand and I'd like to compare data, especially:

Average collected weight per adress per pick-up (every 2 weeks)

Average collected weight per household (= number of households on that adress) per pick-up

Average collected weight per household per year (= previous number * 26 weeks)

Hope this helps. 

Thank you.

[amitchandak · Accepted solution]

@timflo , Try a measure like

AverageX(summarize(Table, Table[Adress], Table[Date], "_1", Sum(Table[Weight])),[_1])

[timflo]

Hi Amitchandak, 

This seemed to have worked! I now have 3 adress lines with the correct values. Can you help me in the next step? 

Adress 1 has 150 apartments

Adress 2 has 43 apartments

Adress 3 has 35 apartments

I created a simple table with 2 colums and the above information and linked the tables based on adress. How can I now:

1. calculate and visualize this average weight per household per pick-up

2. calculate and visualize this average weight per household per year (= the value from 1 times 26 weeks since the pick-ups are every other week)