New Customer by Product

DAX DAX Calculations
1

Hi

I have all the dax as per the workshop on customer analytics - works great - thanks.

I’d like to be able to analyse what the customer actually bought . If I simply pull the product name into the analysis the numbers then don’t work - I assume this is because I don’t have any reference to product in the virtual tables?

Here’s what I have:

New Customer Orders = 
VAR CustomerTM = VALUES( 'Order Intake'[Cust. ID] )
VAR PriorCustomers = CALCULATETABLE( VALUES( 'Order Intake'[Cust. ID] ), 
                        FILTER( ALL( 'Calendar'[Date] ),
                            'Calendar'[Date]> MIN( 'Calendar'[Date] ) - [Churn Time Frame Value] &&
                            'Calendar'[Date] <= MIN( 'Calendar'[Date] ) ) )

RETURN
CALCULATE( [Total Orders],
    EXCEPT( CustomerTM, PriorCustomers ) )

How would I go about adding Product to the available analysis?

2

Can you possibly add some images of the results you are getting? That would make it easier to understand the behaviour of the formula.

If you add the product filter to the formula here it should break the results down per product, so I’m confused there.

If you want to look at what each customer is buying specifically this formula isn’t really going to help you. You would just require a different table/visual with product name as the context and then just total sales.

Will probably need to understand more about the setup you currently have here (images would be helpful) to really help.

3

Table relationship

image
image.png776×759 21.3 KB

Report has a selected year of 2018

If I just have a matrix of Product and CustID

image
image.png840×223 5.1 KB

Each customer has values even though it doesn’t carry across to the Total column

image
image.png846×216 5.32 KB

If I add the Month & Year and New Customer Orders to a matrix I get:

image

but again the total of the column doesn’t equal the £669k if I export to excel the totals are:

image

If I add product to this matrix the total gets worse -

image

The total is always the same in the matrix but nothing adds up to that number?

I haven’t changed anything in the formula just dragged product into the visual

I have the time frame set to 730 days

4

Would have to see the model here, as it could be many things.

One thing to note here is your really jumping in the deep end. This is some of the most advanced DAX you can do.

I detail the technique extensively here

And also here (check out the resource that can be downloaded here as well)

You also shouldn’t really be using this formula everywhere like it looks like you are doing.

DAX formula react very different depending on the context is which you place them it. This formula specifically has been created to work with a date context so over time you can see who are new versus who aren’t.

If you just want to see the products then you’ll want a different formula most likely.

Other than this I would need to see more (as in the model) as it’s difficult to give you a definitive answer from what I’m seeing still.

It comes down to understanding context quite deeply on these.

http://portal.enterprisedna.co/courses/mastering-dax-calculations/lectures/2000594

5

Thanks. I’ve watched all those videos

If we forget about product and just stick to date context ie as per your videos … that doesn’t appear to work either in that when looking at month and year context the total is not the addition of the months?

You mention it could be various things - What areas should I be looking at to check?
The orders table seems very basic and linked to a standard dates table.

I’ve checked on the New Customer Analysis Pack and the same applies. The total doesn’t equal the addition of all the values when state and Month & Year are included.
In the context what does that Total represent?

6

Totals will usually work differently again due to the context of the calculation.

In the total there is usually no context…so you have to think about what is happening with the formula when there is none.

New Customer Orders = 
VAR CustomerTM = VALUES( 'Order Intake'[Cust. ID] )
VAR PriorCustomers = CALCULATETABLE( VALUES( 'Order Intake'[Cust. ID] ), 
                        FILTER( ALL( 'Calendar'[Date] ),
                            'Calendar'[Date]> MIN( 'Calendar'[Date] ) - [Churn Time Frame Value] &&
                            'Calendar'[Date] <= MIN( 'Calendar'[Date] ) ) )

RETURN
CALCULATE( [Total Orders],
    EXCEPT( CustomerTM, PriorCustomers ) )

In the case of the above,

You are taking a set of customers with no context (which mean every single customer from the beginning of time) then comparing that to a customer set between a certain set of dates (which is created by the FILTER logic above)

This is complex as I mentioned earlier, so without actually seeing and testing a demo it’s hard to give you an exact solution here.

By how I originally set up this formula against the month and year context the total was irrelevant to me because I just wanted to show it by month. The reality is the total is pretty irrelevant in a data and time context and what does the total actually give you as an insight. I personally can’t really see one when it that context. In another context maybe, but the formula would likely need to be reworked for that.

If you can create a demo scenario that can work on, that would be helpful.

Thanks