Forum Discussion

N_R_000's avatar
N_R_000
Icon for Helper I rankHelper I
5 years ago
Solved

DAX Calculation: Average Cumulative Spend by 'Days Since'

I have a dataset of [Members] and [Orders] and I need to find the 'Average Spend Per Member', but the bit I'm struggling with is I need that same measure broken down so it can be used in a 'Cumulative Average Spend Since Membership Started'. 

 

Here's a mock-up of my dataset: 

 

The DAX I'm using for the 'Average Spend Per Member' measure is:

AVERAGEX (
SUMMARIZE (
Orders,
Orders[MembershipNo],
"MembershipAverage",
[Total Order Amount]
),
MembershipAverage
)
 
Let's say that shows me the average spend per Membership is £200 (example only). What I'd like to know is; what's the average spend 30 days after membership begins, or 60 days, or 90 days etc. So I'd like a line graph with 'Days Since Membership began' on the X axis, and 'Average Cumulative Spend' on the Y axis, producing a rising trend eventually reaching £200. I just can't get the DAX right to do it, any ideas?  

 

Need Help
  • v-xiaotang's avatar
    v-xiaotang
    5 years ago

    Hi @

    Because not clear about how your data model and table structure look like, so I create a sample.

    You can take steps bellow for reference.

    -

    1. create a column

     

    DaysSinceMembershipBegan column =
VAR _start =
    RELATED ( Membership[MembershipStart] )
RETURN
    DATEDIFF ( _start, ( Orders[Date] ), DAY )

     

    2. create the measure:

    if you want to divide by actual number,

    then create the measure:

     

    _average =
VAR filter_table =
    FILTER (
        ALL ( Orders ),
        Orders[DaysSinceMembershipBegan column]
            <= SELECTEDVALUE ( Orders[DaysSinceMembershipBegan column] )
    )
VAR total =
    CALCULATE ( SUM ( Orders[Value] ), filter_table )
VAR membercount =
    CALCULATE ( DISTINCTCOUNT ( Orders[MembershipNo] ), filter_table )
VAR _div =
    DIVIDE ( total, membercount )
RETURN
    _div

     

    Result:

     

    OR

    if you want to divide by total number in membership table, create this measure

     

    _average1 =
VAR filter_table =
    FILTER (
        ALL ( Orders ),
        Orders[DaysSinceMembershipBegan column]
            <= SELECTEDVALUE ( Orders[DaysSinceMembershipBegan column] )
    )
VAR total =
    CALCULATE ( SUM ( Orders[Value] ), filter_table )
VAR membercount =
    COUNTROWS ( Membership )
VAR _div =
    DIVIDE ( total, membercount )
RETURN
    _div

     

    result:

     Hope this helps.

     

    Best Regards,

    Community Support Team _ Tang

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

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    N_R_000 , try a measure like

    calculate(
    AVERAGEX (
    Values (Orders[MembershipNo])
    [Total Order Amount]
    ),filter(allselected(Orders) , Orders[Date] <=max(Orders[Date]))
    )

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi @

    Because not clear about how your data model and table structure look like, so I create a sample.

    You can take steps bellow for reference.

    -

    1. create a column

     

    DaysSinceMembershipBegan column =
VAR _start =
    RELATED ( Membership[MembershipStart] )
RETURN
    DATEDIFF ( _start, ( Orders[Date] ), DAY )

     

    2. create the measure:

    if you want to divide by actual number,

    then create the measure:

     

    _average =
VAR filter_table =
    FILTER (
        ALL ( Orders ),
        Orders[DaysSinceMembershipBegan column]
            <= SELECTEDVALUE ( Orders[DaysSinceMembershipBegan column] )
    )
VAR total =
    CALCULATE ( SUM ( Orders[Value] ), filter_table )
VAR membercount =
    CALCULATE ( DISTINCTCOUNT ( Orders[MembershipNo] ), filter_table )
VAR _div =
    DIVIDE ( total, membercount )
RETURN
    _div

     

    Result:

     

    OR

    if you want to divide by total number in membership table, create this measure

     

    _average1 =
VAR filter_table =
    FILTER (
        ALL ( Orders ),
        Orders[DaysSinceMembershipBegan column]
            <= SELECTEDVALUE ( Orders[DaysSinceMembershipBegan column] )
    )
VAR total =
    CALCULATE ( SUM ( Orders[Value] ), filter_table )
VAR membercount =
    COUNTROWS ( Membership )
VAR _div =
    DIVIDE ( total, membercount )
RETURN
    _div

     

    result:

     Hope this helps.

     

    Best Regards,

    Community Support Team _ Tang

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