Summaries highest selling item per country

[Anonymous]

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!

[Ashish_Mathur · Accepted solution]

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 dataResult in PowerPivot

[ChandeepChhabra · Accepted solution]

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

[Anonymous]

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

[ChandeepChhabra]

@Anonymous Please go through this blog post 

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

Hope this helps

[Ashish_Mathur · Accepted solution]

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 dataResult in PowerPivot

[Anonymous]

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