Forum Discussion

Mike91's avatar
Mike91
Helper I
1 year ago
Solved

average year sales

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? 

Need Help
Tips and Tricks
  • 123abc's avatar
    123abc
    1 year ago
     

    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 )

     

3 Replies

  • 123abc's avatar
    123abc
    Community Champion
    1 year ago
     

    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's avatar
    123abc
    Community Champion
    1 year ago
     

    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 )