How to Calculate Average Volume a User spend in a Month

[ronin995]

Hello Power Bi Community,

I would like your help to solve how I can visualize in a Bar chart the Avarage volume of money a User spends in a month.
What I tried to use is the following measure: 

AVG Volume per User by Month =

VAR money_ = CALCULATE(

        SUMX(Sales, Sales[products] * (Sales[price]/100)),

        FILTER(

        ALL('Sales'),

        ISONORAFTER('Sales'[created_at], MAX('Calendar'[Date]), DESC) && Sales[status] = "SOLD"

    )

)

VAR AVG_per_User= CALCULATE(

     DIVIDE(money_,[Total Users]),

    FILTER(

        ALLSELECTED('Sales'[created_at]),

        ISONORAFTER('Sales'[created_at], MAX('Calendar'[Date]), DESC)

    )  

)

return AVG_per_User

This is what I get when I put the month and the Year from the calendar table on the X-axis with the Measure in the Y-axis. 

What I need is the avarage of each month separately. Which doesn't count the one from the previous month. How can I adjust it to make it work as I intend?

[v-yanjiang-msft · Accepted solution]

Hi @ronin995 ,

In your formula, you use the ISONORAFTER function, which will include all dates less than the current date in the chart. Modify the formula to:

AVG Volume per User by Month =
VAR money_ =
    CALCULATE (
        SUMX ( Sales, Sales[products] * ( Sales[price] / 100 ) ),
        FILTER (
            ALL ( 'Sales' ),
            EOMONTH ( 'Sales'[created_at], 0 ) = EOMONTH ( MAX ( 'Calendar'[Date] ), 0 )
                && Sales[status] = "SOLD"
        )
    )
VAR AVG_per_User =
    CALCULATE (
        DIVIDE ( money_, [Total Users] ),
        FILTER (
            ALLSELECTED ( 'Sales'[created_at] ),
            EOMONTH ( 'Sales'[created_at], 0 ) = EOMONTH ( MAX ( 'Calendar'[Date] ), 0 )
        )
    )
RETURN
    AVG_per_User

Get the correct result in my sample, I attach my sample below for your reference.

Reference: EOMONTH function (DAX) - DAX | Microsoft Docs

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ronin995]

@v-yanjiang-msft I tried it out and it works as you explained with the example, it was a great learning! Thank you very much!

[ronin995]

@v-yanjiang-msft thanks, i'll try out the solution you gave me right away and let you know!

[amitchandak]

@ronin995 , Based on what I got, Try a measure like

Cumm = CALCULATE(averageX(values('Date'[Month Year]),calculate(SUM(Sales[Sales Amount]))) ,filter(allselected('Date'),'Date'[date] <=max('Date'[date])))

[ronin995]

Hi @amitchandak, unfortunately when I tried to do as you suggested. I get nothing in the chart as it doesn't select any of the data