Reply
Highlighted
Frequent Visitor
Posts: 14
Registered: ‎04-18-2018

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

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.

 

newcustsales.JPG

 

Thank you!

Community Support Team
Posts: 3,055
Registered: ‎07-09-2016

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

@slashfemme,

 

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

Regards,
Sam
Frequent Visitor
Posts: 14
Registered: ‎04-18-2018

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

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.

Senior Member
Posts: 373
Registered: ‎06-23-2016

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

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

Frequent Visitor
Posts: 14
Registered: ‎04-18-2018

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

Hi @Stachu,

 

Thanks for chiming in to help. Smiley Happy 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!

Senior Member
Posts: 373
Registered: ‎06-23-2016

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

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)
			)
		)
	)
)
Frequent Visitor
Posts: 14
Registered: ‎04-18-2018

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

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.

Senior Member
Posts: 373
Registered: ‎06-23-2016

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

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

Senior Member
Posts: 373
Registered: ‎06-23-2016

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

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

Frequent Visitor
Posts: 14
Registered: ‎04-18-2018

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

[ Edited ]

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

 

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!tables.JPG