Find product with more sales

[fabiocovre]

Hello,

I have the following database

Market Name --- Product --- Value

A --- apple --- 15

A --- pineapple --- 20

A --- apple --- 10

B --- apple --- 20

B --- watermelon --- 50

I'd like to return the table below

Market Name --- Product --- Total Sum

A --- Apple --- 45

B --- watermelon --- 70

Can anyone help me?

Thank you!

[pxg08680]

@fabiocovre

Going with your sample data, You need SUM of A and SUM of B whatever the product is. 

Add a column as above and use table visual, Drop MarketName,Value,New column. 

Drop down the value field and select SUM

Regards,

PXG08680

[parry2k]

use table visua and drop market name, product and value in values and next to value, in arrow, choose sum aggregation

[fabiocovre]

Sorry, I didn't understand...

That's what I thought: 

I created a new table with the markets name... and I thought to do a kind of lookup value of the max value to return the product with more sales:

Market Name --- Product

values(Marlet Name) --- lookupvalue (max(value))

A --- Apple

B --- Watermelon

[parry2k]

share you data and expected result

I guess you want sum of market and what is your requirement to show product. is it which product has max value?

[fabiocovre]

The sum isn't the priority. My problem is to create a measure to discover wich product is sold more:

In the case, I'd like to see the result below

Visualization 1

Market Name --- Product

A --- Apple

B --- Watermelon

Visualization 2

Product

Watermelon

[parry2k]

so definition of sold more is based on sum of values, correct?

and also you want it by market and overall as well, correcT?

[fabiocovre]

yes, that's it! sorry if I wasn't very clear in my explanation.

[parry2k · Accepted solution]

Got it, here is what you can do:

Add new measure to get total sales for each product within a market:

Product Sale = Calculate(sum(Fruit[Value]), AllExcept(fruit, Fruit[Market], Fruit[Product])) 

Now let's rank this sale, add new measure

Rank Product = RANKX(ALLEXCEPT(Fruit, Fruit[Market]), [Product Sale])

Add New table visual, drop market, product, product sale and rank product in it and in visual level filter choose where Rank Product = 1

Now 2nd question, get top product across all markets, add following measure:

Top Fruit Product = Calculate(MAX('Fruit'[Product]),  TOPN(1, Fruit, SUM(Fruit[Value]),DESC))

 Drop top fruit product on card visual and you will get "Watermelon"