topic Re: Top 1 Customer by product with sales of each brand in that top outlet in DAX Commands and Tips

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

HarishRathore — Mon, 24 Feb 2020 15:52:10 GMT

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

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

Pragati11 — Mon, 24 Feb 2020 16:56:47 GMT

Hi HarishRathore ,

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

Thanks,

Pragati

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

HarishRathore — Mon, 24 Feb 2020 17:25:31 GMT

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.

Regards

Harish Rathore

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

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

Pragati11 — Mon, 24 Feb 2020 17:30:34 GMT

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

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

Anonymous — Mon, 24 Feb 2020 18:00:15 GMT

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

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

HarishRathore — Tue, 25 Feb 2020 04:31:27 GMT

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

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

Anonymous — Tue, 25 Feb 2020 09:04:24 GMT

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

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

HarishRathore — Tue, 25 Feb 2020 10:07:06 GMT

Then please help me how to achieve the desired result. I want to know the top (number 1 ) customer by brand and how much is the volume. Please help with DAX measure.

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

Anonymous — Tue, 25 Feb 2020 10:42:50 GMT

It's very easy to have a measure that will return the Top 1 customer w.r.t. any measure and the associated values from any measures. If you have such a measure, then to get the Top 1 customer by Brand you just filter by the brand (for instance, placing brands in a column of a visual) and the measure takes care of the rest.

I'll look into this once I get a chance.

Best
D

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

Anonymous — Wed, 26 Feb 2020 03:35:22 GMT

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

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

HarishRathore — Wed, 26 Feb 2020 16:53:23 GMT

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

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

Anonymous — Wed, 26 Feb 2020 17:04:42 GMT

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

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

Anonymous — Wed, 26 Feb 2020 17:05:18 GMT

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

Best
D

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

HarishRathore — Wed, 26 Feb 2020 17:10:40 GMT

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

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

Anonymous — Wed, 26 Feb 2020 17:13:24 GMT

Mate,

Here's a quote from yourself:

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

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

HarishRathore — Wed, 26 Feb 2020 17:35:35 GMT

I dont know what to say.

Anyways I will try to do it myself.

Thanks for your consideration to help me.

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

Anonymous — Wed, 26 Feb 2020 17:40:48 GMT

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

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

Ashish_Mathur — Fri, 28 Feb 2020 03:47:54 GMT

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.

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

HarishRathore — Fri, 28 Feb 2020 04:28:42 GMT

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.

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

Ashish_Mathur — Fri, 28 Feb 2020 04:30:26 GMT

You are welcome.