on 03-27-2019 9:14 AM
Hi all,
I have a simple tab with 4 columns: one dimension, one measure, a rank function applied to the measure and a fourth column for which I'm not getting the right result.
As you can see, I have 3 times rank 1 because the measure is the same for those dimension's value (which is fine).
What I want, for the 4th column, is to display the measure for rank 3.
As the rank 3 does not exist, I want to display the previous rank existing, which is rank 1, so it should diplay the value "36".
Is there a way to create a formula to get this value?
I tried something like this:
= If IsNull(
If IsNull([measure] where ([rank]=3) in report) Then [measure] where ([rank]=2))
Then [measure] where ([rank]=1)
In this case, it works but my data set is quite big and I might have many multiples values for the measure so my formula would be too big if I want to be sure to not miss any multiple value ..
Is there an easier way to do it?
Something like : If IsNull(***) then get previous existing rank value .. This is the logic I want to apply to my variable ..
Thanks in advance for your help!!!
Marc
Sort the table on rank variable in ascending order.After that use below formula in Rank 3 vairable.
=runningcount([Your Rank variable])
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
is there only one measure object in the table? if no, you can try:
= rank([Measure1];[Measure2];([Secteur]))
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
83 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.