Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
slashfemme
Helper I
Helper I

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
v-chuncz-msft
Community Support
Community Support

@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.

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.

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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

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!

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)
			)
		)
	)
)


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

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.

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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

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

 

Stachu
Community Champion
Community Champion

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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.