How to apply the same calculation to multiple values within a month?

Let's say I have a table in my Power BI report which looks like this:

Date       |Month |Offer |Customers |Spend
-----------|------|------|----------|------
2024-01-01 |1     |A     |100,000   |10,000
2024-01-02 |1     |A     |100,000   |20,000
2024-01-31 |1     |B     |150,000   |15,000
2024-02-01 |2     |B     |150,000   |20,000
2024-02-01 |2     |B     |150,000   |30,000

Customers receive each offer at the same time, so in this example, offer A went to 100k customers in total, and offer B to 150k.

Spend is cumulative, so for example, offer B made £5k on 1st Feb 2024 (£20k minus £15k).

I want my report to show the average spend per customer, filterable by month. So for example, if the month of January is selected, the report should display £0.30.

In other words, the report should work out the maximum spend per customer for each offer that is live in the selected month, and add them together. So for January, the calculation becomes:

Offer A: 20,000 / 100,000 = 0.20
Offer B: 15,000 / 150,000 = 0.10
0.20 + 0.10 = 0.30

Does anyone know the best way to do this please?

Solution

You need to get the unique list of offers in the current context, which includes the filter to qpply for the month.

I used the SUMX to iterate and get the max spend for that offer/ the number of customers in the selected month

Average Spend Per Customer = 
SUMX(
    VALUES('Table'[Offer]), 
    VAR CurrentOffer = 'Table'[Offer]
    VAR MaxSpend = CALCULATE(MAX('Table'[Spend]), ALLEXCEPT('Table', 'Table'[Offer]), 'Table'[Month] = SELECTEDVALUE('Table'[Month]))
    VAR CustomerCount = CALCULATE(MAX('Table'[Customers]), ALLEXCEPT('Table', 'Table'[Offer]), 'Table'[Month] = SELECTEDVALUE('Table'[Month]))
    RETURN
        DIVIDE(MaxSpend, CustomerCount, 0)
)