cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
slashfemme Frequent Visitor
Frequent Visitor

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!

10 REPLIES 10
Community Support Team
Community Support Team

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.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
slashfemme Frequent Visitor
Frequent Visitor

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.

Super User
Super User

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?

slashfemme Frequent Visitor
Frequent Visitor

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!

Super User
Super User

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)
			)
		)
	)
)
slashfemme Frequent Visitor
Frequent Visitor

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.

Super User
Super User

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

Super User
Super User

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

slashfemme Frequent Visitor
Frequent Visitor

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

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