Dynamic Ranknumber

Question

Hi&nbsp;I have 6 categories, 20 subcategories and 1 measure.I want to rank the subcategories and the ranking works fine but when I filter on a categorie there's a gap in the ranking. I need 10 to be rank number 5 and 11 to be rank number 6. Is there some way to achieve this? Changing the datamodel, changing the dax formula?&nbsp;My datamodel has:a datedimension which is filtered by year and quartera measuredimension which is filtered by subcategorie&nbsp;&nbsp;&nbsp;&nbsp;The rankmeasure I used:Rank =RANKX (&nbsp;&nbsp;&nbsp; ALLSELECTED ( Measuretable[Subcategorie] );&nbsp;&nbsp;&nbsp; SUM ( Measuretable[Budget] ))
&nbsp;&nbsp;I also tried this formula:Rank =
RANKX (
    ALLSELECTED ( Measuretable[Subcategorie] );
    CALCULATE (
        SUM ( Measuretable[Budget] );
        ALLEXCEPT ( Measuretable; Measuretable[Subcategorie] )
    )
)&nbsp;but still got a gap:&nbsp;&nbsp;Many thanks for anyone who can put me in the right direction!&nbsp;Friendly greets

ashish_mathur · Answer

Hi,
&nbsp;
What result do you get with this?
&nbsp;
Rank =RANKX(ALL(Measuretable[Subcategorie]);SUM(Measuretable[Budget]))

ashish_mathur · Answer

Hi,
&nbsp;
I am still not clear with your question.&nbsp; If you could share the raw dataset and describe the question, it would be great.

svenvu · Answer

Hi&nbsp;It doesn't make a difference in the result. I simplified the example. Actually the measure isn't the "budget" but a more complex measure: ([% spend this year] - [% spend previous year]) * [Budget]&nbsp;The strange thing is: the Rank measure works perfectly with each individual measure. However it does not work anymore with the part "% spend this year"-"%spend previous year".&nbsp;This part is written in dax as follows:&nbsp;DIVIDE (
    CALCULATE (
        SUM ( Measuretable[Spend] );
        FILTER ( ALL ( Date[Quarter] ); Date[Quarter] &lt;= MAX ( Date[Quarter] ) )
    );
    [Budget]
)

-

DIVIDE (
    CALCULATE (
        SUM ( Measuretable[Spend] );
        FILTER ( ALL ( Date[Quarter] ); Date[Quarter] &lt;= MAX ( Date[Quarter] ) )
    );
    [Budget]
)So RANKX must do something with this part that creates more ranknumbers with gaps in it.That is the real life problem that I can't fix.&nbsp;Any help is much appreciated! :)&nbsp;Friendly greets&nbsp;

svenvu · Answer

Hi&nbsp;You can download the pbix file from the link below:Testfile PBIX&nbsp;&nbsp;I need the measure "Rank" to rank the "Subcategorie" according to the measure "Verschil t.o.v. j-1".This measure needs to work with filters from 4 different tables as you can see in the screenshot below.But the tricky part is that I can not have gaps between the rank numbers and at this moment this is the case.&nbsp;As you can see in the screenshot below the ranking works fine, but ranknumber 10 needs to be number 5 and ranknumber 11 needs to be number 6.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;What I have already found out is that the DAX-formula I have used in the measure "Rank" works with almost every other measure. For example "Budget": no gaps in the ranknumbers:&nbsp;&nbsp;I think the gaps are created because of this measure:% Verschil t.o.v. j-1 = 
[% Aangerekend] - [% Aangerekend j-1]% Aangerekend = DIVIDE (&nbsp;&nbsp;&nbsp; CALCULATE (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SUM ( Measuretable[Aangerekend] );&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FILTER ( ALL ( Datum[Kwartaal] ); Datum[Kwartaal] &lt;= MAX ( Datum[Kwartaal] ) )&nbsp;&nbsp;&nbsp; );&nbsp;&nbsp;&nbsp; [Budget])% Aangerekend j-1 = DIVIDE (&nbsp;&nbsp;&nbsp; CALCULATE (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SUM ( Measuretable[Aangerekend] );&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PREVIOUSYEAR ( Datum[Datum] );&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FILTER ( ALL ( Datum[Kwartaal] ); Datum[kwartaal] &lt;= MAX ( Datum[Kwartaal] ) )&nbsp;&nbsp;&nbsp; );&nbsp;&nbsp;&nbsp; CALCULATE (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [Budget];&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PREVIOUSYEAR ( Datum[Datum] )&nbsp;&nbsp;&nbsp; ))
If I change the sign "-" into "*", the measure "Rank" works without showing any gaps in the ranknumber.But ofcourse the output is not what I want as I want to use the sign "-". It's like DAX calculates 2 times and therefore creates 12 possible rankingnumbers instead of 6.&nbsp;It would be extremely awesome if this could be fixed. I refuse to believe that it works for almost every measure except the one I need.&nbsp;Friendly greets&nbsp;&nbsp;&nbsp;

ashish_mathur · Answer

Hi,
&nbsp;
I tried but could not solve the problem.&nbsp; Sorry.

Forum Discussion

Dynamic Ranknumber

6 Replies

Related content

Dynamic Filter

Dynamic RLS

RLS Dynamic

Dynamic Subscription

dynamic insights