Dynamic Ranknumber

[svenvu]

Hi

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?

My datamodel has:

• a datedimension which is filtered by year and quarter
• a measuredimension which is filtered by subcategorie

The rankmeasure I used:

Rank =
RANKX (
    ALLSELECTED ( Measuretable[Subcategorie] );
    SUM ( Measuretable[Budget] )
)

I also tried this formula:

Rank =
RANKX (
    ALLSELECTED ( Measuretable[Subcategorie] );
    CALCULATE (
        SUM ( Measuretable[Budget] );
        ALLEXCEPT ( Measuretable; Measuretable[Subcategorie] )
    )
)

but still got a gap:

Many thanks for anyone who can put me in the right direction!

Friendly greets

[Ashish_Mathur]

Hi,

What result do you get with this?

Rank =RANKX(ALL(Measuretable[Subcategorie]);SUM(Measuretable[Budget]))

[svenvu]

Hi

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]

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".

This part is written in dax as follows:

DIVIDE (
    CALCULATE (
        SUM ( Measuretable[Spend] );
        FILTER ( ALL ( Date[Quarter] ); Date[Quarter] <= MAX ( Date[Quarter] ) )
    );
    [Budget]
)

-

DIVIDE (
    CALCULATE (
        SUM ( Measuretable[Spend] );
        FILTER ( ALL ( Date[Quarter] ); Date[Quarter] <= 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.

Any help is much appreciated! 🙂

Friendly greets

[Ashish_Mathur]

Hi,

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

[svenvu]

Hi

You can download the pbix file from the link below:

Testfile PBIX

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.

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.

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:

I think the gaps are created because of this measure:

% Verschil t.o.v. j-1 = 
[% Aangerekend] - [% Aangerekend j-1]


% Aangerekend = 
DIVIDE (
    CALCULATE (
        SUM ( Measuretable[Aangerekend] );
        FILTER ( ALL ( Datum[Kwartaal] ); Datum[Kwartaal] <= MAX ( Datum[Kwartaal] ) )
    );
    [Budget]
)

% Aangerekend j-1 = 
DIVIDE (
    CALCULATE (
        SUM ( Measuretable[Aangerekend] );
        PREVIOUSYEAR ( Datum[Datum] );
        FILTER ( ALL ( Datum[Kwartaal] ); Datum[kwartaal] <= MAX ( Datum[Kwartaal] ) )
    );
    CALCULATE (
        [Budget];
        PREVIOUSYEAR ( Datum[Datum] )
    )
)

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.

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.

Friendly greets

[Ashish_Mathur]

Hi,

I tried but could not solve the problem.  Sorry.

[svenvu]

Hi @Ashish_Mathur or anyone else who knows RankX

I did some troubleshooting myself and I think I might know the source of the problem. For the positive numbers the ranking works great but as soon as there are negative numbers the RankX creates a gap between the ranknumbers

Does this help to find a solution?

Friendly greets