Daily Average Over Multiple Years

[tl1234]

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 image) but nothing I'm trying gets it right. Any suggestions?

Thanks!

[tl1234 · Accepted solution]

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

[v-hjannapu]

Hi @tl1234,
Glad to hear that you were able to find a solution and get it working.Thank you for sharing what you did, it will surely help others facing the same issue.Please feel free to reach out if you need any help in the future.

Regards,
Community Support Team.

[Ashish_Mathur]

Hi,

Share some data to work with.  Share it in a format that can be pasted in an MS Excel file.  You may also share the download link of the PBI file.

[tl1234]

Hello  @Shubham_rai955 , @Praful_Potphode , @wardy912 , @MFelix,

Thank-you so much for your feedback and apologies for the delayed response - I only work two days a week.

Unfortunately what you proposed is not quite what I'm after. The columns of my query including the datatypes they contain are below.

ProNum (text)

Full Name (ABC 123)

AccomDateIN (date)

AccomDateOUT (date)

occupancy date (date)

Organization (text)

type (ABC 123)

Week (ABC 123)

I know that I could make individual queries for each year of number of people per day, combine all the year queries into one so I have years as columns, then add an average column. But I'm trying to avoid this so that I'm able to keep all the metadata in the columns above associated with the average.

This is why I am trying to get the average by using the visual somehow but if you know of any other way I can do this and keep my metadata for slicers I'd love to try that too.

Thanks!

Tanya

[v-hjannapu]

Hi @tl1234,
Thank you  for reaching out to the Microsoft fabric community forum and no worries about the delay.

I understand your concern now. Since your data is stored at the individual-stay level and you also want to keep all your metadata fields available for slicers, creating separate year queries or fixed average columns will not give the right result. That’s why the visual is not able to calculate a proper daily average across years while still keeping the Year in the legend.

To get the correct daily average and still keep all your metadata, the data needs to be shaped so that each date has one total value per year. Once the data is arranged in that format, you’ will be able to show each year separately and also show one average line that works across all years.

Regards,
Community Support Team.

[v-hjannapu]

Hi @tl1234,

I wanted to follow up and see if you have had a chance to review the information that was shared. If you have any additional questions or need further clarification, please don’t hesitate to reach out. I am here to assist with any concerns you might have.

Regards,
Community Support Team.

[v-hjannapu]

Hi @tl1234,

I would also take a moment to thank @Shubham_rai955 , @Praful_Potphode , @wardy912 , @MFelix for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Regards,
Community Support Team.

[Shubham_rai955]

To calculate and display the daily average across multiple years in Power BI, you need a measure that aggregates the data correctly and then divides by the number of years. Here's a simple approach:

Steps to Calculate Daily Average Across Years:

1. Create a measure that sums the total count for each day across all available years.

2. Count distinct years in your data.

3. Divide the total daily count by the number of years to get the average.

[Praful_Potphode]

Hi @tl1234 ,

did you try creating column(as shown in your image) and plotted the column in line chart?

the formula for new column may look like below:

Average = DIVIDE(YourTable[2023]+YourTable[2024],2)

the visualization may look like below.

The axis is not sorted.but you can sort it as per your requirements.also you can change line style to dotted/dashed/custom as per your requirements.

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

[wardy912]

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 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!

[wardy912]

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!

[MFelix]

Hi @tl1234 ,

In this case you need to remove the year from the legend and then use the average in your line chart this will return what you need.

What is the measure you are using in the values?

[tl1234]

Hello Miguel,

Thanks for getting back to me!

I need to have the year in the legend to be able to get the separate columns for each year in the visual table.

The values are a count of peoples' names who stayed with us on each day.

I've added a couple snips below.

Thank-you,

Tanya