Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 ...
  • OwenAuger's avatar
    OwenAuger
    6 years ago

    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 IDPath DescriptionPathItemTypeDays
    1Apt, Townhome, House1Apt385
    1Apt, Townhome, House2Townhome400
    1Apt, Townhome, House3House600
    2Villa, House1Villa365
    2Villa, House2House500
    3Apt, Townhome, House1Apt400
    3Apt, Townhome, House2Townhome90
    3Apt, Townhome, House3House285
    4Townhome, Villa, House1Townhome200
    4Townhome, Villa, House2Villa160
    4Townhome, Villa, House3House390
    5Apt, Townhome, House1Apt90
    5Apt, Townhome, House2Townhome180
    5Apt, Townhome, House3House400

     

    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

OwenAuger's avatar
OwenAuger
Super User
6 years ago

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 IDPath DescriptionPathItemTypeDays
1Apt, Townhome, House1Apt385
1Apt, Townhome, House2Townhome400
1Apt, Townhome, House3House600
2Villa, House1Villa365
2Villa, House2House500
3Apt, Townhome, House1Apt400
3Apt, Townhome, House2Townhome90
3Apt, Townhome, House3House285
4Townhome, Villa, House1Townhome200
4Townhome, Villa, House2Villa160
4Townhome, Villa, House3House390
5Apt, Townhome, House1Apt90
5Apt, Townhome, House2Townhome180
5Apt, Townhome, House3House400

 

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's avatar
    Anonymous
    Not applicable
    6 years ago

    Thanks Owen!! This worked perfectly!