Hello Team,
I am relatively new to Power Bi and have really enjoyed the courses Sam has provided.
The question is how can I get my average unit sales when there are blank months?
The issue is it averages only the months that have totals.
I have currently set up a total sales divided by 12 to solve this problem but I’m sure there is a better way!
Hi @dean.alley,
Can you explain what you want to show as average for the month when there is a blank or what end result you are expecting. ?
Regards
Sorry I will give more detail.
Some of my months have no sales in a particular month.
This creates a problem because the DAX only picks up the months that have data which results in ana incorrect average.
What I need is for the average to be take over 12 months whether there is data or not.
I hope this clarifies the situation better?
Can you try this measure…
Monthly Average Updated =
VAr A = [Total unit Sales] //this will hold the sum/count of units as per the logic in measure
VAr B = 12 //this will always give 12. Doing this to give you average at year level.
return
A/B
//this is not a dynamic measure and will return value at yearly level only. If you wish to use it at diferent levels than i would suggest you to provide a sample pbix and the result you want to achieve.
Regards,
Hemant
Hi @dean.alley,
To make it more dynamic you could do something like this:
Avg Sales by Month =
VAR NoMonths = COUNTROWS( VALUES( Dates[Month & Year] ))
RETURN
DIVIDE( [Total unit Sales], NoMonths )
.
Keep in mind that this calculates the number of months including both the month for start- and end date
I hope this is helpful
Thank you for your solution Hemantsingh.
Hello Melissa,
That worked perfectly thank you.
I’m only starting to get my head around DAX and thought there would be a solution around count or Distinct Count I just wasn’t sure how to tie it together.
I really like the use of VAR to tie it all together thank you.
It’s great to know that you are making progress with your query @dean.alley. Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!