How To Calculate Ranking In A Hierarchical Form

YouTube / Blogs / Other
1

Dear Harsh

May I ask for a question that where we can find the resources related to Youtube video of yours titled with " How To Calculate Ranking In A Hierarchical Form" if it is uploaded in one of our training courses at EDNA?

Ranking in a hierarchical form
Ranking in a hierarchical form1273×887 113 KB

While I am trying with my dataset, it is surprisingly getting true at the wrong calculation which you showed us just for understanding the case. So, may I just look around the dataset you carried out and test on it?

Ranking.pbix (651.2 KB)

Thank you.

Best Regards
Khin Kye Sin

2

Hello @KhinKyeSin,

Thank You for posting your query onto the Forum.

Attached is the file provided for the reference which I’ve used in the video.

Upon checking your file, I wasn’t able to find any discrepancy.

Hoping you find the attached PBIX file helpful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Calculation Of Ranking In A Hierarchical Form - Harsh.pbix (354.3 KB)

3

Hello @Harsh

Thank you so much for your kindness and support.

I can practice them in your file and it comes out as the way it should be.

Still, in my file, it has been right as with the wrong measure. In other words, the second rank “2” has been still keep startingly right even it is drilled down to the next level and not changing to “3” like in the example of youtube video or in your dataset file.

Besides, may I request you to kindly instruct me to better learn about the difference between “Addcolumn” and “Summarize”.

I always though that “AddColumn” and “Summarize” are somewhat similar that “Addcolumn” is just adding another column to its reference table and it would be completely same when it is referencing to the virtual table of Summarize as in Right Measure (in the video).

I mean, I thought these two measures will come out the same result;

Ranking_Territory =

Sumx(

ADDCOLUMNS(

 SUMMARIZE(

           'Location Master','Location Master'[States and Regions]),

            "Ranking",

                RANKX(All('Location Master'[States and Regions]),[Total Sales],,DESC)), [Ranking])

Ranking_Territory =

Sumx(

 SUMMARIZE(

           'Location Master','Location Master'[States and Regions],

            "Ranking",

                RANKX(All('Location Master'[States and Regions]),[Total Sales],,DESC)), [Ranking])

So earlier I thought that why you don’t just use only Summarize by having measure in the Summarize itself and now, it figured out that it would be wrong if we just used “Summarize”.

Would you kindly briefly explain why we have to use “AddColumn” or why having measures of Ranking in Summarize don’t work out in here? Or just kindly instruct me where should I review lessons at EDNA training?

Thank you so much.

Best Regards
Khin Kye Sin.

4

Hello @KhinKyeSin,

Still, in my file, it has been right as with the wrong measure. In other words, the second rank “2” has been still keep startingly right even it is drilled down to the next level and not changing to “3” like in the example of youtube video or in your dataset file.

The reason why it’s working in your file is because the relationship which is being created between the tables are not inserting any “HIDDEN” blank values inside the data model. When hidden blank values take place inside the data model, it disrupts this kind of calculations.

Now, coming to your next query -

I always though that “AddColumn” and “Summarize” are somewhat similar that “Addcolumn” is just adding another column to its reference table and it would be completely same when it is referencing to the virtual table of Summarize.

The reason why I used “ADDCOLUMNS()” alongwith “SUMMARIZE()” is because “SUMMARIZE()” function is executed into a filter context corresponding to the values in the grouped columns. That is, if a column is created inside the “SUMMARIZE()” function itself then, in that case, everything will be considered as one single set of group. So for example, let’s execute a below provided query and see the results in a physical table -

Table Based On SUMMARIZE Function =
SUMMARIZE(
    'Location Master' ,
    'Location Master'[States and Regions] ,
    "@Total Sales" , [Total Sales] ,
    "@Ranking" , RANKX( ALL( 'Location Master'[States and Regions] ) , [Total Sales] , , DESC ) )

Results Based On SUMMARIZE Function

As you can observe in the above screenshot, all the rows are being ranked as 1. That’s because, the entire table is being summarized as one single group.

But now, on the other hand, when you create those exact same virtual columns inside the “ADDCOLUMNS()” section. See what happens when the below query is executed -

Table Based On ADDCOLUMNS with SUMMARIZE Function =
ADDCOLUMNS(
    SUMMARIZE(
        'Location Master' ,
        'Location Master'[States and Regions] ) ,
    "@Total Sales" , [Total Sales] ,
    "@Ranking" , RANKX( ALL( 'Location Master'[States and Regions] ) , [Total Sales] , , DESC ) )

Results Based On ADDCOLUMNS with SUMMARIZE Function

As you can observe in the above screenshot, now, all the rows are being ranked properly. But why did this happened actually? So the reason is, “ADDCOLUMNS()” function operates in a “ROW Context” that does not automatically propagate into a filter context.

And this is why, I used the “ADDCOLUMNS()” function alongwith “SUMMARIZE()” in order to evaluate the results. Secondly, when we use this type of method, it’ll also ignore all the hidden blank values, if there are any, which is being brought inside the data model because when we summarized the table, it’ll only consider the values which are actually present inside the data model. So if my model contains any physical blank values i.e., any value which is being kept as blank by me then it’ll consider the blank otherwise hidden values will be ignored.

I’m also providing a link below pertaining to the topic - “Best practices using SUMMARIZE and ADDCOLUMNS” for the reference purposes.

Hoping you find this explanation useful and strengthens your understanding about this concept.

Thanks and Warm Regards,
Harsh

5

Hello @Harsh

Can’t thankful enough for your reply.

Thank you so much.

Best Wishes
Khin Kye Sin

6

Hello @KhinKyeSin,

You’re Welcome!!! :slightly_smiling_face:

I’m glad that I was able to assist you.

Thanks and Warm Regards,
Harsh