Averages by String

[Anonymous]

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!

[OwenAuger · Accepted solution]

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

[Anonymous]

Thanks Owen!! This worked perfectly!