return the quantity of assets from the max sum(Price) from a category

[thanhphongle]

Hello Community,

This is my database:

Category	Product	Price
Laptop	Asus Zen	300
Laptop	Sony f12x	400
Laptop	Apple Mac Pro	800
PC	Alienware Pro	2000
PC	HP Gaming X4	1500
Mouse	Razor Diamondback	50
Keyboard	Logitec Wireless	30
Mouse	Logitec G5	60
Headset	Creative GX 8	80

I want QV to return me quantity of assets considering the max sum(Price) of a Category.

In this case it should return me:

Category = PC and Quantity = 2

This is my expression which i m using. But somehow it is not working correctly.

Concat(DISTINCT {<Category = {"=Aggr(Sum(Price), Category) = Max(TOTAL Aggr(Sum(Price), Category))"}>} Categoy, ', ') & ' \n'

& Max(TOTAL Aggr(count(Category), Category))

Could anyone help me out here?

[MK_QSL]

What result you are looking for? if 409 is not correct?

[MK_QSL]

='Max Category-Quantity' & CHR(10) &

CONCAT({<Category = {"=RANK(SUM([Selling price]),4)=1"}>}DISTINCT Category,', ') & CHR(10) &

COUNT({<Category = {"=RANK(SUM([Selling price]),4)=1"}>}Category)

[MK_QSL]

or

='Max Category-Quantity' & CHR(10) &

CONCAT({<Category = {"=RANK(SUM({<Category>}[Selling price]),4)=1"}>}DISTINCT Category,', ') & CHR(10) &

COUNT({<Category = {"=RANK(SUM({<Category>}[Selling price]),4)=1"}>}Category)

[thanhphongle]

I dont get it. In your attached file it is working. I exactly copied the expression into my real database with the same attributes. But it doesnt work there... I dont know why it still returns me quantity = 61 instead of 947. Is there anything what i have to consider in the database?

[sunny_talwar]

Won't know until I see it. Manish do you have suggestions?

[thanhphongle]

I tried the expression of Manish as well. Same problem

[MK_QSL]

We need to see your real file... without looking into it, can't find out what's going wrong !

[MK_QSL]

Are you sure that the data structure is same in your real application compare to the data you have provided as sample file?

[thanhphongle]

I attached a test database close to the main database with exactly the same problem. This file is locked

[sunny_talwar · Accepted solution]

This still works:

Count({<Category = {"=Aggr(Sum([Selling price]), Category) = Max(TOTAL Aggr(Sum([Selling price]), Category))"}>}[Serial Number])