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
Anonymous
Not applicable

Summaries highest selling item per country

Hi Guys,

 

I have a dataset im trying to train on optrained  from here. I am trying to create a summary table outlining the highest selling item for each country i.e. 

 

Afghanistan --> 10 Household orders

Albania        --> 8 Persoanl Care orders

Algeria         --> 6 Baby Food, Cereal, Vegetables orders (Display all 3 categories as there is a tie) 

 

Any advice on how it can be done would be greatly appreciated. I'm primarily not looking for the solution but rather some pointers on how this can be achived. 

 

Thanks in advance!

2 ACCEPTED SOLUTIONS
Ashish_Mathur
Super User
Super User

Hi,

I have presented multiple solutions to this problem - Determine the top selling location for each product.  Just fous on the last tab there (PowerPivot solution).  With this modified DAX formula, you will get the desired result (as you can see in the screenshot)

=CONCATENATEX ( TOPN ( 1, VALUES ( Data[Location]), [Highest revenue] ), Data[Location],", " )

This can also be solved using the Query Editor, if you so wish.

Original dataOriginal dataResult in PowerPivotResult in PowerPivot


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

View solution in original post

ChandeepChhabra
Impactful Individual
Impactful Individual

Hi @Anonymous 
Have written 3 measures to solve your problem. Please download the Power BI file here

 

Capture.PNG

View solution in original post

5 REPLIES 5
ChandeepChhabra
Impactful Individual
Impactful Individual

Hi @Anonymous 
Have written 3 measures to solve your problem. Please download the Power BI file here

 

Capture.PNG

Anonymous
Not applicable

Thank you all for reply!

@ChandeepChhabraI have just looked at your solution and it seems to be doing exactly what I was looking for.

 

I am however unsure about how/why its working. 

Could you possibly explain what the formula is doing?

 

Thanks

@Anonymous Please go through this blog post 

https://www.goodly.co.in/top-selling-product-analysis-in-powerpivot/

 

Hope this helps

Ashish_Mathur
Super User
Super User

Hi,

I have presented multiple solutions to this problem - Determine the top selling location for each product.  Just fous on the last tab there (PowerPivot solution).  With this modified DAX formula, you will get the desired result (as you can see in the screenshot)

=CONCATENATEX ( TOPN ( 1, VALUES ( Data[Location]), [Highest revenue] ), Data[Location],", " )

This can also be solved using the Query Editor, if you so wish.

Original dataOriginal dataResult in PowerPivotResult in PowerPivot


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

Do you have a preference in completing this Power Query or do you want to strictly use DAX?

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.