Total Sales and Average spend

DAX DAX Calculations
1

Hello!

I am trying to figure out Sales that came from new customers that enrolled and placed orders in a cycle . Once I have that, I can create measures to see the % of sales that come from the new customers vs total sales, Average spend, etc.

image
image721×312 10.4 KB

Attached is the sample file and any help with this is really appreciated. Retention with Sales.pbix (7.0 MB)

Thanks,
Nipuna

2

Hi @nipunadv,

For this type of calculation you can resuse the new customer count pattern, it just requires a small modification as you can see in the example below.

Sales C+01 = 
VAR FirstCycle = MINX( ALLSELECTED( CALENDAR[CycleID] ), [CycleID] )
VAR _Cycle = MAX( CALENDAR[CycleID] )
VAR FirstCust =
    CALCULATETABLE( 
        VALUES( CUSTOMERS[CustomerID] ), 
        TREATAS( VALUES( CALENDAR[PeriodID] ), CUSTOMERS[EntryPeriodID] ),
        CALENDAR[CycleID] = FirstCycle
    )
VAR CustOrders = 
    CALCULATETABLE( 
        VALUES( SALES[CustomerID] ), 
        CALENDAR[CycleID] = _Cycle +1,
        ALL( CALENDAR[CycleText] )
    )
VAR NewCustomers = INTERSECT( FirstCust, CustOrders )
RETURN

CALCULATE( [Sales],
    NewCustomers
)

.

I hope this is helpful.

1 Like
3

Hi @nipunadv, did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

4

Thank you so much @Melissa! the formula worked but I think the numbers are not coming out right.
If I go to the table view of Customers table, and filter out EntryPeriodID 1011-1014 (2019-C6), image

I get 26 NewCust, 2019-C06 Enrolled.xlsx (9.3 KB) which is correct and they are New Customers, enrolled in Cycle 2020-C6. Sales for the same cycle is correct as well.

image

I created another tab (Order Details), and move the slicer to 2019-C07 (To find the orders placed by new customers who enrolled previously), total sales there does not match the sales from measures.

image
image662×729 35.6 KB

image
image659×736 34.7 KB

image
image830×277 22.2 KB

To clarify, again, I went to the data table to find out the exact number of New Customers, enrolled in a Cycle, then I copied those IDs and searched for their orders in the same cycle, 2019-C6 matches Sales C+00 but Sales C+01 amount does not match the total Order Details, when I select 2019-C7 (Sales of New Customers enrolled in C6, and placed orders in C7.

Retention with Sales.pbix (7.0 MB)

5

Melissa’s answer is super close, but something is still missing. Please see below.

6

Hi @nipunadv,

Okay clear. See if this meets your requirement.

Sales C+01 = 
VAR FirstCycle = MINX( ALLSELECTED( CALENDAR[CycleID] ), [CycleID] )
VAR _Cycle = MAX( CALENDAR[CycleID] )
VAR FirstCust =
    CALCULATETABLE( 
        VALUES( CUSTOMERS[CustomerID] ), 
        TREATAS( VALUES( CALENDAR[PeriodID] ), CUSTOMERS[EntryPeriodID] ),
        CALENDAR[CycleID] = FirstCycle
    )
VAR CustOrders = 
    CALCULATETABLE( 
        VALUES( SALES[CustomerID] ), 
        CALENDAR[CycleID] = _Cycle +1,
        ALL( CALENDAR[CycleText] )
    )
VAR NewCustomers = INTERSECT( FirstCust, CustOrders )
RETURN

CALCULATE( [Sales],
    NewCustomers,
    FILTER( ALL( 'CALENDAR'[CycleID] ),
        CALENDAR[CycleID] = _Cycle +1
    ),  ALL( CALENDAR[CycleText] )
)

.

image

1 Like
7

That was perfect! Thank you so much! :pray:t5:

One last question, since I am trying to get the Average spent, I could calculate the average, within the same formula above, instead of making bunch of Sales measures (Sales C+01, Sales C+02,…) and dividing from the number of customers ( Sales C+01/C+01), correct?