Bottom Selling Product Dynamically

[olaoyesunday]

Hi All,

I wanted to dynamically calculate the bottom-selling product in my application. Let's say I want the 10 bottom-selling products based on sales amount. I created a variable to be used in my input box, named vBottomSellingProducts. In my straight table, I chose 'Product Name' as my dimension, and I used 'Sales Amount' as my measure. I used the following formula in my expression:

=aggr(if(rank(sum([Sales Amount])) <= vBottomSellingProducts, sum([Sales Amount]), null()), [Product Name]) as Amount

Then, I used it in the sort tab on 'Product Name' in ascending order. If I insert 10, for example, into my input box, it would start from the smallest price against the product, say $3,971,546.00, in ascending order. However, if I enter a higher figure, it would start from a price lower than the 3 million above say 242,894.96, in ascending order, as shown in the two screenshots below, if I enter 1 it would give me higher figure 225,357,584.50 as also shown below which is not what I wanted.. Please, what is the correct formula to use to give me the bottom-selling products based on sales amount, in case I enter 1, 2, 5 or 50 etc  products in my input box, so that it will still give me the same bottom-selling products?"

10 bottonmselling product1 bottom selling product50 bottom selling product

[vincent_ardiet_]

Try this:
sum({<[Product Name]={"=Rank(sum([Sales Amount]))<=vBottomSellingProducts"}>}  [Sales Amount])

[olaoyesunday]

@vincent_ardiet_ Thanks very much, I used the formular above is still giving me the same thing. or is there anything I ought to check or uncheck in my Sort tab?

[vincent_ardiet_]

Try to add this measure and see what this is returning
 Rank(sum([Sales Amount]))

[olaoyesunday]

After applying the formular, It was returning these in my screenshot below:

[vincent_ardiet_]

Ok, so now if you replace with this expression (hardcoded rank threshold):

sum({<[Product Name]={"=Rank(sum([Sales Amount]))<=10"}>}  [Sales Amount])

[olaoyesunday]

When I applied the formular, it returns what is shown in the screenshot below, but when I put one in the formular sum({<[Product Name]={"=Rank(sum([Sales Amount]))<=1"}>}  [Sales Amount]) it returns the number I ringed red, which obviously is not what I wanted.

[vincent_ardiet_ · Accepted solution]

Sorry I've noticed you want them in ascending order. So just multiply by -1 the sum in the rank:
sum({<[Product Name]={"=Rank(-sum([Sales Amount]))<=vBottomSellingProducts"}>}  [Sales Amount])

[olaoyesunday]

@vincent_ardiet_  

Thanks very, it works!