## Desktop

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.

Thank you!

Community Support Team
Posts: 2,677
Registered: ‎07-09-2016

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

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.

Established Member
Posts: 170
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. 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!

Established Member
Posts: 170
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.

Established Member
Posts: 170
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

Established Member
Posts: 170
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.

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!