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.
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
Solved! Go to Solution.
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
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
Thanks JJ, It worked. Really your explanation was good.
Regards,
Shailesh
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 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |