Total per month / average per month

[Gabry]

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

[Gabry · Accepted solution]

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.

Link to the Pbix file 

thank you

I came up with a solution but I don't think it's very elegant

1) 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: 

https://1drv.ms/u/s!AuzBMgyXs40fgWI8GdFjunClTHoL?e=rWyAoF

[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.

[Anonymous]

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. 

[ryan_mayu]

could you pls provide the pbix file or provide the sample data not the screenshot.

[Gabry]

Also, this is my calendar table: