topic Re: Averages by String in DAX Commands and Tips

Averages by String

Anonymous — Tue, 11 Feb 2020 15:51:36 GMT

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!

Re: Averages by String

OwenAuger — Wed, 12 Feb 2020 05:03:50 GMT

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

Re: Averages by String

Anonymous — Thu, 13 Feb 2020 15:36:38 GMT

Thanks Owen!! This worked perfectly!