New customer measure getting filtered by product slicer = do not want. :( Just want # of brand new.

[slashfemme]

Hello!

I've created the following measure for new customers:

New Cust Sales =
VAR Customer = ALL(sales[Customer Number])
RETURN
CALCULATE([Total Sales], FILTER(Customer, CALCULATE(COUNTROWS(Sales), FILTER(ALL(Dates), Dates[Date] < MIN(Sales[Date])))=0))

I have two slicers on my page: one for Fiscal Year and one with a list of Product Types ("GFS"). The new customer sales work the way I want when sliced by date, but when I make a selection on product type I am getting a return of customers who are new TO THAT PRODUCT. They could have purchased many times before, just never that product. What I really want is the number of customers who are brand new to the company who are purchasing that particular product. Is there a way to code this into my existing measure?

In the picture below, the total ($61K) is from legitimately new customers. I would love for each GFS product category to actually list the $ in brand new customer sales.

Thank you!

[v-chuncz-msft]

@slashfemme,

You may try using VALUES ( sales[Customer Number] ) instead.

[slashfemme]

Hi, @v-chuncz-msft,

Thanks for the reply! I tried Values instead of All but it still returns the same data if the product category slicer is selected. Dang.

[Stachu]

does adding ALL(Product) in the inner CALCULATE as second filter parameter solve it?

[slashfemme]

Hi @Stachu,

Thanks for chiming in to help. 🙂 When I write my forumula like this:

Test New Cust Sales =
VAR Customer = VALUES(sales[Customer Number])
RETURN
CALCULATE([Total Sales],FILTER(Customer, CALCULATE(COUNTROWS(Sales), FILTER(ALL(Dates), Dates[Date] < MIN(Sales[Date])),ALL(Products))))

It returns the total sales for the entire selected product category, disregarding the new customers all together. Is this where you meant the placement of ALL('Products')?

Thank you!

[Stachu]

I think it should be one bracket earlier:

Test New Cust Sales =
VAR Customer = VALUES(sales[Customer Number])
RETURN
CALCULATE(
	[Total Sales],
	FILTER(
		Customer, 
		CALCULATE(
			COUNTROWS(Sales),
			FILTER(ALL(Dates), Dates[Date] < MIN(Sales[Date]),
			ALL(Products)
			)
		)
	)
)

[slashfemme]

Maaaaan, it still returns the whole sum of sales for that product category as before. Someday I'll get this - it has to be do-able.

[Stachu]

can you share the sample tables with the joins explained? that would help for sure

[slashfemme]

Oh man, pleas excuse my tablescape here, as this is my first big attempt at rolling something useful out of PowerBI. 🙂

Basically, I have a sales fact table, with a Dates, Products and Customers table. Secondary tables are for Geographies and GFS (product type) so I can sort by either if a single value is needed.

Dates -> Sales (joined on sales date)

Products -> Sales (joined on item number)

Customers -> Sales (joined on customer number)

Geographies - > Customers (on country) -> Sales

GFS -> Products (on GFS code) -> Sales

Thanks!

[Stachu]

also - how do you define a new customer? if someone bought something last week is he still new, or already old? where is the threshold for new/old customers

[slashfemme]

So, I have three years of purchasing data. I would ideally like to define a new customer as someone who has not purchased anything at all in any prior year to the one currently selected.