Forum Discussion
Gabry
Super User
Super UserTotal per month / average per month
Hello, I searched on the forum but I still didn't understand how to solve my issue. I would like to calculate the total quantity ordered by month, then get the average quantity ordered per month...
Hi Guys, thank you all for the replies. Last night I was confused and my issue was logical.
The average quantity ordered per month is a single value, and can't be splitted into single rows of a column. It's just one single value, to obtain it you have to divide the Total Orders by the number of months.
So my DAX formula is as simple as:
Total Orders by month = CALCULATE([Total Orders], GROUPBY('Calendar','Calendar'[Year Month]))But now I'm wondering, what if in the calendar there are months where there have been no orders?I shouldn't be dividing by all the months of the calendar but only by the month in which there started to be orders.thank youI came up with a solution but I don't think it's very elegant1) Create a new column in the Purchase table,Purchased Month = FORMAT(Purchase[PurchaseDate], "YYYY MM")2) Count the number of month in that column =Number of months with purchase = DISTINCTCOUNT(Purchase[Purchased Month])3) Calculate the average quantity per month =AVG Quantity per Month2 = DIVIDE([Total Orders], [Number of months with purchase])Here is my solution:
GabrySuper User
Super UserAlso, this is my calendar table: