Showing only Bottom N values in chart

[Shailu198]

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

[DoubleJ · Accepted 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

[Shailu198]

Thanks JJ, It worked. Really your explanation was good.

Regards,

Shailesh

[DoubleJ]

@Shailu198 glad it works!