Forum Discussion

tl1234's avatar
tl1234
Helper I
9 months ago
Solved

Daily Average Over Multiple Years

Hello,   I've made the below visual which represents the total number people staying at our facility every day split up by year. What I need is a daily average across years (shown in red text in im...
Need Help
  • tl1234's avatar
    tl1234
    8 months ago

    Hello,

    Sorry again for the delay. Unfortunately these solutions did not work for what I wanted so I ended up doing it the way that would lose the metadata but kept the main numbers. So I ended up making a seperate query for each year grouped by the main categories we want, merging the queries together, then adding average columns. Not ideal but it worked. I'll just have to make separate visuals for the metadata.

    Thank-you,

    Tanya

wardy912's avatar
wardy912
Super User
9 months ago

Hi tl1234 

 

 First, you'll need to unpivot your data. You may want to duplicate your current table. In Power query, perform the following steps:

 

Select your year columns, go to transform and select 'unpivot columns'

 

This will give you Date, Attribute (Change column name to year), and Value

 

This will give you a table that will dynamically add any additional years that get added to your data as it grows.

close and apply.

 

Now add the following measure (Edit for your table name)

 

AveragePerDate =
CALCULATE(
    AVERAGE(HotelData[Value]),
    ALLEXCEPT(HotelData, HotelData[Date])
)

This will give you the average for each date across all years

 

 

--------------------------------

I hope this helps, please give kudos and mark as solved if it does!

 

Connect with me on LinkedIn.

Subscribe to my YouTube channel for Fabric/Power Platform related content!