Forum Discussion
Super UserTotal per month / average 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:
Super UserHi,
Create a Calendar Table with calculated column formulas for Year, Month name and month number. Sort the Month name by the Month number. Create a slicer and select a Year. To your visual, drag Month from the Calendar Table. Write this measure and drag it to the visual
Total quantity sold = sum(Data[Quantity])
Write this measure and drag it to a card visual
Average quantity sold = SUMX(VALUES(Calendar[Month name]),[Total quantity sold])
Hope this helps.