Forum Discussion
Showing only Bottom N values in chart
Hi All,
I want to show bottom 5 values based on count. Let me clarify more:
There are two columns - product_id & product. I want to show bottom 5 products based on count(product_id). Means which the product appears very less time in the column should be 1st & so on. But the problem is i have 22 products where the count is 1 and when i am doing bottom N filtering, all 22 products are appearing (since all have same value). I want only 5 out of those 22.
Any help would be appreciated.
Regards,
Shailesh
Hi
The following might be a solution. If I got you right, a product can be multiple times in your table.
1. Create A Measure "ProductsCount"
ProductsCount = COUNTROWS(Products)
2. The question now is: Which 5 of the 22 products shall be displayed? I created a column "SortValue" that takes the ProductId in account. Multiplying the "ProductsCount" measure by 1000000 and then adding the ProductID gives each Product a unique "SortValue". Of course this approach only works if the ProductID is an integer value.
SortValue = [ProductsCount]*100000 + Products[ProductId]
3. You can now create a visual with the Bottom 5 filter set on "SortValue"
Of course the formula for the "SortValue" formula depends on your actual data.I hope this helps!
JJ
3 Replies
- DoubleJ
Solution Supplier
Hi
The following might be a solution. If I got you right, a product can be multiple times in your table.
1. Create A Measure "ProductsCount"
ProductsCount = COUNTROWS(Products)
2. The question now is: Which 5 of the 22 products shall be displayed? I created a column "SortValue" that takes the ProductId in account. Multiplying the "ProductsCount" measure by 1000000 and then adding the ProductID gives each Product a unique "SortValue". Of course this approach only works if the ProductID is an integer value.
SortValue = [ProductsCount]*100000 + Products[ProductId]
3. You can now create a visual with the Bottom 5 filter set on "SortValue"
Of course the formula for the "SortValue" formula depends on your actual data.I hope this helps!
JJ
- DoubleJ
Anonymous glad it works!