Forum Discussion
Averages by String
Hello,
I am trying to find an average based on a string value. Here is a little background information:
1. I am trying to find the average days residents spend during their time in a residence
2. Residents can live in apts, townhomes, villas or houses
3. I got the data to show their "path" and the "number of days" they spent at each place, so the data looks like this:
| Resident ID | Path | Days in Each Path |
| 1 | Apt, Townhome, House | 385, 400, 600 |
| 2 | Villa, House | 365, 500 |
| 3 | Apt, Townhome, House | 400, 90, 285 |
| 4 | Townhome, Villa, House | 200, 160, 390 |
| 5 | Apt, Townhome, House | 90, 180, 400 |
Now, I am trying to get an average number of days in each location, so I would want to average the days that resident 1, 3, and 5 had together. My goal table would look like this
| Path | Number of Residents | Average Days in each Residence |
| Apt, Townhome, House | 3 | 291, 233, 428 |
| Villa, House | 1 | 365, 500 |
| Townhome, Villa, House | 1 | 200, 160, 390 |
Any help would be much appreciated!!
Thank you!
Hi Anonymous
I have shared a sample PBIX here.
I would recommend you transform your data into this form to make the calculations easier:
Resident ID Path Description PathItem Type Days 1 Apt, Townhome, House 1 Apt 385 1 Apt, Townhome, House 2 Townhome 400 1 Apt, Townhome, House 3 House 600 2 Villa, House 1 Villa 365 2 Villa, House 2 House 500 3 Apt, Townhome, House 1 Apt 400 3 Apt, Townhome, House 2 Townhome 90 3 Apt, Townhome, House 3 House 285 4 Townhome, Villa, House 1 Townhome 200 4 Townhome, Villa, House 2 Villa 160 4 Townhome, Villa, House 3 House 390 5 Apt, Townhome, House 1 Apt 90 5 Apt, Townhome, House 2 Townhome 180 5 Apt, Townhome, House 3 House 400 I have done this in Power Query in the above PBIX.
Then create measures:
Number of Residents = DISTINCTCOUNT ( Residence[Resident ID] ) Average Days = AVERAGE ( Residence[Days] ) Average Days Concatenated = IF ( HASONEVALUE ( Residence[Path Description] ), // ensure just one Path Description is selected CONCATENATEX ( VALUES ( Residence[PathItem] ), ROUND( [Average Days], 0), ", ", Residence[PathItem] ) )After doing this, you can create a table similar to the one you posted:
Anyway that is how I would approach this. Hopefully that's of some use 🙂
Regards,
Owen
2 Replies
Hi Anonymous
I have shared a sample PBIX here.
I would recommend you transform your data into this form to make the calculations easier:
Resident ID Path Description PathItem Type Days 1 Apt, Townhome, House 1 Apt 385 1 Apt, Townhome, House 2 Townhome 400 1 Apt, Townhome, House 3 House 600 2 Villa, House 1 Villa 365 2 Villa, House 2 House 500 3 Apt, Townhome, House 1 Apt 400 3 Apt, Townhome, House 2 Townhome 90 3 Apt, Townhome, House 3 House 285 4 Townhome, Villa, House 1 Townhome 200 4 Townhome, Villa, House 2 Villa 160 4 Townhome, Villa, House 3 House 390 5 Apt, Townhome, House 1 Apt 90 5 Apt, Townhome, House 2 Townhome 180 5 Apt, Townhome, House 3 House 400 I have done this in Power Query in the above PBIX.
Then create measures:
Number of Residents = DISTINCTCOUNT ( Residence[Resident ID] ) Average Days = AVERAGE ( Residence[Days] ) Average Days Concatenated = IF ( HASONEVALUE ( Residence[Path Description] ), // ensure just one Path Description is selected CONCATENATEX ( VALUES ( Residence[PathItem] ), ROUND( [Average Days], 0), ", ", Residence[PathItem] ) )After doing this, you can create a table similar to the one you posted:
Anyway that is how I would approach this. Hopefully that's of some use 🙂
Regards,
Owen