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:
Hi Gabry ,
According to your description, here are my steps you can follow as a solution.
(1)This is my test data.
(2)Create a matrix,and you will see:
For the related .pbix file,pls see attached.
You can refer to the following posts that may be helpful to you:
Solved: Calculate average count per month - Microsoft Power BI Community
Solved: Running Total and calculating the average for the ... - Microsoft Power BI Community
Solved: Compare Total per month vs average per month per y... - Microsoft Power BI Community
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.