Forum Discussion
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
This is my Sales table:
How can I create a measure that calculate the average quantity ordered per month?
Thank you guys
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:
5 Replies
ryan_mayucould you pls provide the pbix file or provide the sample data not the screenshot.
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.
- Ashish_Mathur
Hi,
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.
- Gabry
Also, this is my calendar table:
- Gabry
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:
