Forum Discussion
Monthly Average that Changes Based on Date Selection
Hi everyone,
I have my monthy average measure for my Sales found in my fSales table, but I need to ammend it so that is automatically averaging based on what dates I am currently filtering/including from my dDate table. For instance, if I was filtering for just Jan and Feb 2023, all of my Sales values would be divided by two. If this changes to Jan - Mar 2023, all of my Sales would be divided by three. My dDate includes a breakdown of the date by Month column. Any ideas for what seems quite a simple problem?
Many thanks
Hi,
This measure pattern should work
Average monthly sales = AVERAGEX(VALUES(ddate[Month name]),[Total sales])
I have assumed that Total sales is a measure that you have written. To your slicer, drag Month name from the ddate table.
4 Replies
- Ritaf1983
Super User
Hi Anonymous
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 - Ashish_Mathur
Hi,
This measure pattern should work
Average monthly sales = AVERAGEX(VALUES(ddate[Month name]),[Total sales])
I have assumed that Total sales is a measure that you have written. To your slicer, drag Month name from the ddate table.
Thank you for your responses, but after further checks it seems this average is not applying correctly when there is no underlying sales for products within at least some of the selected date range. For instance, I have certain products with sales in only 10 months of 2023. When I apply my date range for the whole of 2023, the overall sales for theses products should still be divided by 12 but instead only seem to be dividing by 10. What needs to be adjusted it seems is to superimpose the number of months I am filtering to define the monthly average (i.e. the denominator), regardless of whether there are sales for products in said months within my fSales table.
Please note that sometimes there are simply no entries (or rows) of data to be found whatsover within such months for the products, but it could also be the case that there is data but just no sales are listed against it (i.e. the Sales column is blank).
Do you have any ideas how I can acheive this? Thanks
- Ashish_Mathur
Hi,
Share some data to work with and show the expected result under various scenarios (especially the one that you have mentioend in your last paragraph).
