Get value from another column using an iterator function.

[Anonymous]

Hello guys, I'm pretty new to Power BI and DAX been watching videos and reading some sqlbi articles to understand some things better.

I have a table which I managed to group by the categories of items sold, and managed to get the most sold item. With my current formula I get the value of the most sold item, but I really don't know how I might be able to get the name of the product, instead of the value.

An example of Orders BY Products table is:

CategoryID	Product name	Quantity Sold
1	X1	1155
1	Y1	145
1	Z1	125
2	X2	725
3	Y3	885
3	Z3	987
2	Y2	875

My formula: 

New Table = VAR groupByCategory = GROUPBY( 'Orders BY Products', 'Orders BY Products'[CategoryID], "Best sold", 
    MAXX( CURRENTGROUP() , 'Orders BY Products'[Quantity sold] ) ) RETURN groupByCategory

 Returns a table like this:

CategoryID	Best Sold
1	1155
2	725
3	987
...	...

While I would like to show:

CategoryID	Best Sold
1	X1
2	Y2
3	Z3
...	...

Any help would be much appreciated.

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Create two measures

Measure 1 = SUM(Table1[Quantity Sold])

Measure 2 = RANKX(ALLEXCEPT(Table1,Table1[CategoryID]),[Measure 1],,DESC,Dense)

Add measure 2 in the visual level filter of a table visual as below

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@Anonymous add product name in your expression

New Table = VAR groupByCategory = GROUPBY( 'Orders BY Products', 'Orders BY Products'[CategoryID], 
'Orders BY Products'[ProductName], "Best sold", 
    MAXX( CURRENTGROUP() , 'Orders BY Products'[Quantity sold] ) ) RETURN groupByCategory

[Anonymous]

Hi @parry2k , I tried the solution, but it returns Orders BY product table.