cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HarishRathore
Helper II
Helper II

Top 1 Customer by product with sales of each brand in that top outlet

Hi,

I have a data model in power pivot where I wanted top  customer per product using this measure : 

 

Top Customer Name = Maxx(TopN (1, Summarize ( Customers, Customers[Customers Name], "Vol", [Total Volume]), [Vol], DESC ), Customers[Customers Name])

 

I am getting desired result but only thing is when i am putting brand in row field and the said measure in value field then i am getting result but the moment put "Total Volume" in value field then i am getting the complete volume of that particular brand against that top outlet but I want that what is volume of that brand against that top outlet. Can you please help me in this regards????

Regards

 

1 ACCEPTED SOLUTION

Hi,

I think these measures work

Top Customer = FIRSTNONBLANK(TOPN(1,VALUES(Customer[CustomerKey]),[Sales Amount]),1)
Top Customer sales = SUMX (TOPN(1,VALUES(Customer[CustomerKey]),[Sales Amount]),[Sales Amount])

Hope this helps.

Untitled.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

22 REPLIES 22
Pragati11
Super User
Super User

Hi @HarishRathore ,

 

Can you please share some screenshots along with smaple data for the problem that you are facing?

 

Thanks,

Pragati


My Blog: Data Vibe
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




Hi Pragati,

 

Kindly find screen shot for the same. You can see that sales amount of product is coming same in both table but when i am selecting top 1 customers of each product then i should get sales amount of that particular customer and of that particular product.

Capture.PNG

 

Regards

Harish Rathore

P.S. - This data is for example purpose only.

Anonymous
Not applicable

Customers is a dimension that stores info about your customers and is connected to your fact table.

// Bear in mind that the attribute
// Customer Name should be unique
// in the Customers table. If it's not,
// then you'll need to use a different
// variation of the measure [Top Cust Name 2]
// which will use CustomerID that by definition
// is unique as the key to the dimension.
[Top Cust Name] =
	topn(1,
		values( Customers[Customer Name] ),
		[YourMeasure]
	)
	
[Top Cust Name 2] =
var __topCustId =
	topn(1,
		values( Customers[CustomerId] ),
		[YourMeasure]
	)
var __custName =
	calculate(
		values( Customers[Customer Name] ),
		Customers[CustomerId] = __topCustId
	)
return
	__custName
	

[Top Cust Measure] =
var __topCustId =
	topn(1,
		values( Customers[CustomerId] ),
		[YourMeasure]
	)
var __result =
	calculate(
		[YourMeasure],
		Customers[CustomerId] = __topCustId
	)
return
	__result

 

Always create correct models and the DAX will be simple and predictable. Otherwise... You'll be creating hell for yourself and your reports' consumers.

 

Best

D

Hi @Anonymous , You have not mentioned product anywhere in your measure. How would I get needed result then?

 

Please find the link for .pbix file.

Link for file - https://chav5nz-my.sharepoint.com/:u:/g/personal/office883_chav5nz_onmicrosoft_com/ER9wcSnsDdxGtdV3ynUwdr0BC-OCc7VylYBqmyJfxXJguQ?e=GjUJ5O

Can you please try to work on this and share the solution? I want to know the top (number 1 ) customer by brand and how much is the volume. Please help with DAX measure.

 

Regards

Harish Rathore

Hi,

I think these measures work

Top Customer = FIRSTNONBLANK(TOPN(1,VALUES(Customer[CustomerKey]),[Sales Amount]),1)
Top Customer sales = SUMX (TOPN(1,VALUES(Customer[CustomerKey]),[Sales Amount]),[Sales Amount])

Hope this helps.

Untitled.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Thanks @Ashish_Mathur a lot for this. I thought that this was never gonna solve. But you made it so easier for me. Super Kudos.

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur , Can i get "City" of top customers? When i am taking "City" from  "Cutomer" table then i am not getting correct result. Can i get it from DAX itself like - Top customer name with city? Please help.

Hi,

In this DAX formula =Top Customer = FIRSTNONBLANK(TOPN(1,VALUES(Customer[CustomerKey]),[Sales Amount]),1)

simply replace the VALUES(Customer[CustomerKey]) with the table and column name which has the City.  If you do not get the desired result, then share limited data in an Excel file and show me yoru desired result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Mate,

 

Here's a quote from yourself:

 

I am getting desired result but only thing is when i am putting brand in row field and the said measure in value field then i am getting result but the moment put "Total Volume" in value field then i am getting the complete volume of that particular brand against that top outlet but I want that what is volume of that brand against that top outlet. Can you please help me in this regards????

 

OK. Please read this out to yourself slowly and several times. Do you still think this makes any sense?

 

Especially read this:

 

then i am getting the complete volume of that particular brand against that top outlet but I want that what is volume of that brand against that top outlet.

 

If you give such descriptions, then no wonder you don't get answers you're after...

 

Please try to word your requirements comprehensibly and logically. For people to be able to help you they have to be able to UNDERSTAND the problem at hand. COMPRENDO? Good.

 

Best

D

I dont know what to say.

Anyways I will try to do it myself.

Thanks for your consideration to help me.

Anonymous
Not applicable

@HarishRathore, you don't have to say anything.

All I'm asking you to do is to state your requirements clearly and logically. If you don't do this, people will not be able to help you - it's as easy as that. I thought this was obvious.

So, do not get offended, as there's no need for that, but try to make what you want crystal clear. And please think about what and how you phrase your questions and explanations.

If you don't agree with what I wrote above, then please tell me where I'm wrong. If I'm wrong, I'll put some ash on my head and apologize without hesitation.

Best
D
Anonymous
Not applicable

If you filter by brand, you'll get the top 1 customer and their sales.

Best
D

There are 100 of brands, i think its not wise to put brands in slicer because i need to get only top 1 outlet with sale. If i put slicer for brands then it would not be good visual. when i am putting brands in row and select "Top Customers" then I am getting the perfect result but the moment I select volume then i am getting total volume of brand. 

 

I have run of all options thats why seeking help.

Regards

Harish Rathore

Anonymous
Not applicable

Because a product should be selected via a slicer or placed on a row/column.

The measures I gave you get you the top customer with respect to any slicing you have performed. Same for the other measure. Mate, take a good look at it.

Best
D
Anonymous
Not applicable

Hi there. Mate, what you're trying to achieve is not possible with the current setup. [Top Customer] as a measure, not a member of a dimension. You have to create a measure [Top Customer Sales] and then drop onto your visual.

Best
D

Hi @HarishRathore ,

 

Can you try changing your DAX as follows:

 

Top Customer Name = Maxx(

                                               TopN (

                                                           1, Summarize (

                                                               Customers, Customers[Customers Name], Customers[Product Name], "Vol", [Total Volume]

                                                                                  ),

                                                             [Vol], DESC

                                                         ),

                                              Customers[Customers Name])

 

If this helps please give Kudos and mark it as a Solution! 🙂

 

Thanks,

Pragati


My Blog: Data Vibe
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




Hi,

Thank you for this solution, this really worked for me.

(I used it to get the location with highest count of sales per customer).

But in my dataset I am facing some ties and now the outcome seems random.

Would it be possible to get the location with the highest count in sales and in case of ties, look at the location (from the ties) where the purchasedate was most recent?

Thanks in Advance!

Hi, I am trying to put Customers[Product Name] but only thing is that I have product name in product master and not in customer master hence Summarize is not accepting it. Please help.
Regards
Anonymous
Not applicable

You should have relationships between your products, customers and the fact table. If you do, then SUMMARIZE does work with different tables.

By the way, you should not use SUMMARIZE to calculate anything within it. This is a VERY DANGEROUS function that should only be used to give you the existing combinations of values from a (expanded) table. There is an article about it on www.sqlbi.com, which is the ultimate source of wisdom about DAX. Instead of SUMMARIZE you sohuld use a combination of SUMMARIZE/ADDCOLUMNS. If you choose to ignore what I've just written, be prepared to produce numbers which you will not be able to explain. Easy as that.

You've been warned.

Best
D

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!