average year sales

[Mike91]

Hi All,

i need your help to calculate the average sales during fyscal year for each item, for example item A sold 1000 euro in 12 month, average 83,33 euro, how i can divide the total sales for the month where the item sold? 

[Mike91]

Thanks a lot for your help 🙂 all works

[123abc]

To calculate the average sales per month during a fiscal year in Power BI, but only for the months where the item actually had sales, you can use a DAX measure. This measure will:

1. Calculate the total sales for the fiscal year.
2. Count the number of months where there were sales for that item.
3. Divide the total sales by the number of months with sales.

Here’s how you can write the DAX measure:

Steps:

1. Total Sales: Sum the sales for the item.
2. Number of Months with Sales: Count the distinct months where sales occurred.
3. Average Sales Per Month: Divide the total sales by the count of months with sales.

DAX Formula:

DAX

Copy code

Average Sales Per Month = VAR SalesMonths = CALCULATE( COUNTROWS( DISTINCT(Sales[Month]) ), Sales[Total Sales] > 0 -- Only count months where there were sales ) VAR TotalSales = SUM(Sales[Total Sales]) -- Total sales of the item RETURN IF( SalesMonths > 0, TotalSales / SalesMonths, -- Calculate average if there are sales months 0 -- Return 0 if there were no sales )

 

[123abc · Accepted solution]

To calculate the average sales per month during a fiscal year in Power BI, but only for the months where the item actually had sales, you can use a DAX measure. This measure will:

1. Calculate the total sales for the fiscal year.
2. Count the number of months where there were sales for that item.
3. Divide the total sales by the number of months with sales.

Here’s how you can write the DAX measure:

Steps:

1. Total Sales: Sum the sales for the item.
2. Number of Months with Sales: Count the distinct months where sales occurred.
3. Average Sales Per Month: Divide the total sales by the count of months with sales.

DAX Formula:

DAX

Copy code

Average Sales Per Month = VAR SalesMonths = CALCULATE( COUNTROWS( DISTINCT(Sales[Month]) ), Sales[Total Sales] > 0 -- Only count months where there were sales ) VAR TotalSales = SUM(Sales[Total Sales]) -- Total sales of the item RETURN IF( SalesMonths > 0, TotalSales / SalesMonths, -- Calculate average if there are sales months 0 -- Return 0 if there were no sales )