Forum Discussion
Group 'other' in TopN
Hi all,
Im new to DAX and would like som help with this task that i think should be easy but i can´´t get my head around it.
I would like to create a piechart with top 5 products based on a measure and then group the rest of the products into 'Other'
Product
Product 1 10
Product 2 20
Product 3 40
Product 4 30
Product 5 50
Product 6 60
Product 7 70
Measure: COUNT ( OrderID)
Expected result: Product 3-7 with product name and value, and 'others' with sum of 30 ( product 1 + product 2)
Product 3 40
Product 4 30
Product 5 50
Product 6 60
Product 7 70
Other 30
I tried RANKX(product, CALCULATE(COUNT(OrderID)) and got the rank value, but now i don´t know how to produce the 'other' group and visualize it together with the other products.
I´m using Direct query.
Thanks in advance!
3 Replies
- lbendlin
Super User
var a= RANKX(...)
return if (a<6,[Product Name],"Other")
Hi Anonymous,
You can simply use if statement with count rows function to add a category of 'top n' records.
Category= IF ( COUNTROWS ( FILTER ( 'Table', [Amount] >= EARLIER ( 'Table'[Amount] ) ) ) <= 5, [Product] & "", "Other" )Then you can use this column and raw table amount fields with aggregate mode 'sum' to achieve your requirement.
Regards,
Xiaoxin Sheng
