Average or Sum of purchases per customer

[trankillity]

Hi all,
I'm new to these forums and to BI analysis in general (having previously done manual calculations in SSMS based on requests).

I have a specific problem that I'm scratching my head over: From a fact table of line item sales with customer keys, how do I determine the average/total spent grouping by customers in a monthly period (either 30 days rolling or fixed fiscal month breaks)? I'm guessing it's likely to be some combination of SUMMARIZE, CALCULATE, SUMX and AVERAGEX but I'm having trouble wrapping my head around those concepts.

If someone has any idea on how this would best be accomplished and can explain why it works to increase my understanding, I'd be very appreciative.

The structure of the inputs are as follows:
'Purchase'
[CustomerKey]
[DateKey]
[TransactionID]
[StoreKey]
[SKU]
[Qty]
[Price]

'Calendar'
[DateKey]
[FullDate]
[FiscYear]
[FiscMonth]

 

[trankillity]

I believe that I may have worked it out. Correct me if I'm wrong but the following should be what I'm looking for when determining the average amount spent by a customer per month:

AvgSpendPerCustPerMonth :=<br><span class="Keyword" style="color:#0070FF">AVERAGEX</span><span class="Parenthesis" style="color:#D0D0D0"> (</span><br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">SUMMARIZE</span><span class="Parenthesis" style="color:#D0D0D0"> (</span><br><span class="indent8">        </span>'Purchase',<br><span class="indent8">        </span>'Purchase'[CustomerKey],<br><span class="indent8">        </span>'Dates'[FiscMonth],<br><span class="indent8">        </span><span class="StringLiteral" style="color:#D93124">"Value"</span>, <span class="Keyword" style="color:#0070FF">SUM</span><span class="Parenthesis" style="color:#D0D0D0"> (</span> 'Purchase'[Value] <span class="Parenthesis" style="color:#D0D0D0">)</span><br><span class="indent4">    </span><span class="Parenthesis" style="color:#D0D0D0">)</span>,<br><span class="indent4">    </span>[Value]<br><span class="Parenthesis" style="color:#D0D0D0">)</span><br>

It's not a rolling 30-day average or anything, but just grouping it by month should give a similar result.

 

[trankillity]

Just realised this was aggregating data for transactions that didn't have a member assigned (-1 CustomerKey) so have excluded those from the calculation using the following. Looks to be much more accurate.

Avg$/MoMem :=<br><span class="Keyword" style="color:#0070FF">AVERAGEX</span><span class="Parenthesis" style="color:#D0D0D0"> (</span><br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">SUMMARIZE</span><span class="Parenthesis" style="color:#D0D0D0"> (</span><br><span class="indent8">        </span><span class="Keyword" style="color:#0070FF">FILTER</span><span class="Parenthesis" style="color:#D0D0D0"> (</span> 'Purchase', Purchase[CustomerKey] <> <span class="Number" style="color:#EE7F18">-1</span> <span class="Parenthesis" style="color:#D0D0D0">)</span>,<br><span class="indent8">        </span>'Purchase'[CustomerKey],<br><span class="indent8">        </span>'Dates'[FiscMonth],<br><span class="indent8">        </span><span class="StringLiteral" style="color:#D93124">"Value"</span>, <span class="Keyword" style="color:#0070FF">SUM</span><span class="Parenthesis" style="color:#D0D0D0"> (</span> 'Purchase'[Value] <span class="Parenthesis" style="color:#D0D0D0">)</span><br><span class="indent4">    </span><span class="Parenthesis" style="color:#D0D0D0">)</span>,<br><span class="indent4">    </span>[Value]<br><span class="Parenthesis" style="color:#D0D0D0">)</span><br>