Forum Discussion

admin_xlsior's avatar
admin_xlsior
Post Prodigy
4 years ago
Solved

Monthly average including no transactions

Dear friends,

 

When I have a transactions that in some case the month is skip means no activity in certain month, and I'm averaging my transaction using this DAX =

 

 

avg.monthly sales = AVERAGEX(
                    VALUES(Dates[Month]),
                    [net sales]
                    )

 

 

 

how to get the average factor to be the whole numbers of months ?

The Dates dimension is the usual date table which of course have all complete months, but because my transaction is for example have skip month like :

MonthNet sales
May 202150
Jun 2021100
August 2021200

 

For monthly average shouldn't be it is 250 / 4 instead 250 /3 ?

Also because the months is deliberately chosen from slicer, so I did choose June 2021, provided it has value or not, I think I want all selected month to be included in the average. 

 

Thanks

  • Assuming your Dates table doesn't have missing months, you can likely solve this just by adding "+ 0".

    avg.monthly sales = AVERAGEX ( VALUES ( Dates[Month] ), [net sales] + 0 )

2 Replies

  • Assuming your Dates table doesn't have missing months, you can likely solve this just by adding "+ 0".

    avg.monthly sales = AVERAGEX ( VALUES ( Dates[Month] ), [net sales] + 0 )
    • admin_xlsior's avatar
      admin_xlsior
      Post Prodigy

      Hmm, haven't thought about that. I actually just change my formula to just use DIVIDE with COUNTROWS(AllSelected(Date[month]))

       

      I will try your approach, it looks like a great idea.

       

      Thanks!