Forum Discussion
Daily Average Over Multiple Years
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
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
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.
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.