Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
@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
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |