Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Solved! Go to Solution.
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.
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.
@N_R_000 , try a measure like
calculate(
AVERAGEX (
Values (Orders[MembershipNo])
[Total Order Amount]
),filter(allselected(Orders) , Orders[Date] <=max(Orders[Date]))
)
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |