How to use rank to get bottom 5

[annabergendal]

In my pivot table, I have used calulated dimension to get top 5 of a sum in a variable, like this:

if(aggr(rank($(vActiveProductsR12)),[Produkt lev])<=5,[Produkt lev])

Can I do the same to get bottom 5?

[anbu1984]

Yes. Uncheck suppress zero

[annabergendal]

Here is a sample,

Shouldn't this give a list of bottom three? Why only one?

Thanks.

[MK_QSL]

What result you are looking for? Can you provide in Excel file?

[anbu1984]

Unchecked suppress zero in the attached qvw

[annabergendal]

Thanks!

That helped the get all bottom 3.

In my application though, I still have problem with the total of each Lev, it is not the sum of the rows but Expression total.

If I do a straight table and specify number of rows in Total mode, it gets correct.

But in pivot table, it's dimmed out.

I already checked that Indent mode in Style is unchecked, I also tried to check Show partial sums for dimensions.

Is it even possible to change Total mode with a calculated dimension?

Thanks.

[anbu1984]

Is this expected output?

[annabergendal]

I'd like to be able to change Total mode for Expression to Sum of Rows, instead of Sum of Expression as default.

How can I do this?

In my application, which unfortunately I cannot share, there is a difference between the two, and I need to get Sum of Rows.