Average sales per customer based on total customers as at 30 June

Hi,

So my data table is like this

Quarter Sales Services
Sep-18 120 10
Dec-18 130 11
Mar-20 140 12
Jun-20 150 13
Sep-19 160 14
Dec-19 170 15
Mar-20 180 16
Jun-20 190 17

And I need some dax to be able to create a formula that sums all of the sales for the financial year, and divides it by the number of services as at the June quarter only.

So for 18-19 I need = sum(120, 130,140, 150) / 13
For 19-20 I need = sum(160, 170,180,190) / 17

I am thinking now that I need to use variables, and max for date.

The data is pre-aggregated so counts of customer ID etc doesn’t work.

YTD formulas don’t work as it sums all of the services over the full financial year. Whereas I only need it at the end of the financial year, and preferably in a way that makes the financial year flexible so I can filter on it.

Any assistance would be appreciated.

Thanks in advance.

John

@jgriffit,

I get the gist of this, but I’m a bit confused by your data above. Should the 3rd and 4th rows be 19, not 20?

  • Brian

@jgriffit,

Assuming I have the correction right above, here’s one approach. Because your data is at a monthly granularity, not daily, there are (at least) two reasonable approaches to take with this: 1) pull the FY over from the Dates table via Power Query, or 2) create a virtual relationship between the tables via TREATAS using Month&Year. I opted for the simplicity of #1, but #2 is probably better if you’re going to be doing additional calculations on this Data Table.

Here are the two relevant measures - the first calculates the Max services per FY, and the second calculates your desired metric by FY:

Max Services = 

CALCULATE(
    MAX( Data[Services] ),
    Data[Month] = 6,
    ALL( Data),
    VALUES( Data[Fiscal Year] )
)

Tot Sales Div Max Svcs = 

VAR TotSalbyFY =
CALCULATE(
   [Total Sales],
    ALLEXCEPT(
        Data,
        Data[Fiscal Year]
    )
)

VAR Quotnt =
DIVIDE(
    TotSalbyFY,
    [Max Services],
    0
)

RETURN
Quotnt

Here’s what it looks like put together:

image

I hope this is helpful. Full solution file attached

Hi @jgriffit, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

Many thanks Brian. Really helpful.