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]

=FirstSortedValue(Category,-Aggr(SUM(Price),Category))

and

=COUNT({<Category = {"=Rank(SUM(Price),4)=1"}>}Category)

[sunny_talwar]

Try these:

=Only({<Category = {"=Rank(Sum(Price)) = 1"}>}Category)

=Count({<Category = {"=Rank(Sum(Price)) = 1"}>}Category)

[thanhphongle]

This is not really working. I have another database which is much more bigger. And in this case it shows me less assets that it should have in the database. Unfortunetly I cant attached my database here. Thats y i have to post some common examples. Can you find a solution for my expression which i post on top? I m using this expression in a Textbox and not in a chart.

[sunny_talwar]

This seems to be good:

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

May be change the second part to this:

=Max(TOTAL Aggr(count({<Category = {"=Aggr(Sum(Price), Category) = Max(TOTAL Aggr(Sum(Price), Category))"}>}Category), Category))

Because right now it is showing the Max Count rather than the count for max price category

[thanhphongle]

Ok it doesnt return me 0 anymore, but it still doesnt return me the correct quantity

[thanhphongle]

Your solution is working fine for my upper example. But if i apply you solution to my main database it says:

Category = Notebook , Sum(Price) = 900000 , Quantity= 61

But 61 is not correct. I checked my database. It should be 947.

Is there anything in the expression which i have to consider?

Additionally i created a table chart with Category as dimension and count(Category) as expression:

If i choose Notebook in a listbox. it returns me 947. So i think sth is still wrong in the expression

[sunny_talwar]

If there is only one category with max sum of price, can you try this:

Count({<Category = {"=Aggr(Sum(Price), Category) = Max(TOTAL Aggr(Sum(Price), Category))"}>} Category)

If this still doesn't work, would you be able to share a sample where it isn't working?

[thanhphongle]

I attached a qv file

[sunny_talwar]

Check out the attached