Forum Discussion
How to create a table with average weights by group
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!
timflo , Try a measure like
AverageX(summarize(Table, Table[Adress], Table[Date], "_1", Sum(Table[Weight])),[_1])
Hi,
Please check the below picture and the attached pbix file.
It is for creating a new table.
5 Replies
- amitchandak
Super User
timflo , Try a measure like
AverageX(summarize(Table, Table[Adress], Table[Date], "_1", Sum(Table[Weight])),[_1])
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)
- Jihwan_Kim
Hi,
Please check the below picture and the attached pbix file.
It is for creating a new table.
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.
