Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
Hi @HarishRathore ,
Can you please share some screenshots along with smaple data for the problem that you are facing?
Thanks,
Pragati
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.
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
Exactly what I needed, thank you!
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.
You are welcome.
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.
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.
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
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
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |