Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?"
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])
Try this:
sum({<[Product Name]={"=Rank(sum([Sales Amount]))<=vBottomSellingProducts"}>} [Sales Amount])
@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?
Try to add this measure and see what this is returning
Rank(sum([Sales Amount]))
After applying the formular, It was returning these in my screenshot below:
Ok, so now if you replace with this expression (hardcoded rank threshold):
sum({<[Product Name]={"=Rank(sum([Sales Amount]))<=10"}>} [Sales Amount])
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.
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])
Thanks very, it works!