Dynamic RANKS using CARDS elements

[bajimmy1983]

Hello Community, how are you? 

Could you please help me? PBIX file I am using.

Each Client participates in one or more than one GLOBAL GROUP. There are 02: HAVAS CREATIVE and HAVAS MEDIA. Under Global Group we can have Groups (BETC, HMG and HWW).

Desire outcome using cards: When I select a single Client, but do not select any Global Group nor Group, ranking will be considered as overall, comparing all Clients with each other. When I add a selection with Global Group ranking will drill down to this level and the same situation when I select Client + Global Group + Group.

Considering what I have in pbix file:

*** 1 Matrix results considering as example client "RB" (All correct):

• Overrall ranking = 2
• + Selecting Global Group as HAVAS CREATIVE = 1;
• + Selecting Group as HWW = 1;
• + Selecting Group as BETC = None (because RB does not belong to this Group).

 *** 2 Slicer results selecting client "TIM" and using both "Z_Rnk_Individual" and "Z_Rnk_Overall" measures (Wrong😞

• Overrall ranking = 2 (correct should be 1 as Matrix);
• + Selecting Global Group as HAVAS MEDIA = 2 (correct should be 1 as Matrix);
• + Selecting Group as HMG = 2 (correct should be 1 as Matrix). 

Measures:

Z_Rnk_Individual =
IF (
    ISBLANK ( [Tot Bil Pilot BRL] );
    BLANK ();
    RANKX (
        ALL ( Pilot[CLIENT] );
        CALCULATE ( [Tot Bil Pilot BRL]; ALLSELECTED ( Pilot[CLIENT] ) );
        ;
        0;
        DENSE
    )
)

Z_Rnk_Overall =
IF (
    ISBLANK ( [Tot Bil Pilot BRL] );
    BLANK ();
    RANKX ( ALL ( Pilot[CLIENT] ); [Tot Bil Pilot BRL];; 0; DENSE )
)

Thank you so much in advance for your time and support. I hope you can help me to keep moving forward in this journey 🙂

[v-shex-msft]

Hi @bajimmy1983,

Can you please share some sample data to test? I can't download the pbix file which you shared.

Regards,

Xiaoxin Sheng

[bajimmy1983]

Hi @v-shex-msft, sorry to delay my reply. I am very busy these days :), but It will be helpfull if you can help me learn a little more.

I am attaching again PBD sample file and also Excel file (sample data base I have used) as you requested. 

Thanks a lot for your time and sorry again for my delay.

Jaderson (AKA Jimmy)

[v-shex-msft]

Hi @bajimmy1983,

Can you share them to onedrive ? Based on policy reason, I can't find the files at you shared link.

Regards,

Xiaoxin Sheng

[bajimmy1983]

Hi @v-shex-msft,

Sure I can. Please find both files at One Drive sample files.

Thanks a lot again,

Jaderson (Jimmy)

[v-shex-msft]

Hi @bajimmy1983,

You can try to use below formula, it will auto switch the compare level based on group level slicer.

Z_Rank Dynamic = 
	IF(ISBLANK([Tot Bil Pilot BRL]),BLANK(),
		IF(ISFILTERED(Pilot[GLOBAL GROUP])&&ISFILTERED(Pilot[GROUP]),//check if isfiltered global group and group at same time
		RANKX(ALL(Pilot[CLIENT]),[Tot Bil Pilot BRL],,0,Dense),//client
		RANKX(ALLSELECTED(Pilot[CLIENT]),[Tot Bil Pilot BRL],,0,Dense))//global
		)

Regards,

Xiaoxin Sheng

[bajimmy1983]

Hi @v-shex-msft, sorry again for delaying my reply. So many things at same time.

So, I have tried as you suggested, but the problem persists when I have all Slicers selected (last print below). Maybe I am doing something wrong yet.

Let's take RB client as example: It must be 2nd in the overall rank (which is correct), but inside its Global Group (in this case HAVAS CREATIVE) Z_Rank Dynamic measure is ranking it as 2nd the same way as overall (which is incorrect).

The correct should be 2nd as overall and 1st inside Global Group (HAVAS CREATIVE) and Group (HWW) selections. I really tried to fix it, but it seems very difficult to me yet. Sorry! I will not give up. Of course there is a way to do so and sonner or later we will discover.

In your suggested measure, I just changed [Tot Bil Pilot BRL] for [Invest Pilot BRL] because the second is the right for our situation. The rest is the same. 

Measure: 

Z_Rank Dynamic = 
IF (
    ISBLANK ( [Invest Pilot BRL] );
    BLANK ();
    IF (
        ISFILTERED ( Pilot[GLOBAL GROUP] ) && ISFILTERED ( Pilot[GROUP] );
        //check if isfiltered global group and group at same time
        RANKX (
            ALL ( Pilot[CLIENT] );
            [Invest Pilot BRL];
            ;
            0;
            DENSE
        );
        //client
        RANKX ( ALLSELECTED ( Pilot[CLIENT] ); [Invest Pilot BRL];; 0; DENSE )
    )
)

Screens:

Correct

Correct

Correct

Wrong

Thanks again for your time 🙂

[v-shex-msft · Accepted solution]

Hi @bajimmy1983,

>>So, I have tried as you suggested, but the problem persists when I have all Slicers selected (last print below). Maybe I am doing something wrong yet.

After I modify the formula from your steps, it still works on my side:

Z_Rank Dynamic = 
	IF(ISBLANK([Invest Pilot BRL]),BLANK(),
		IF(ISFILTERED(Pilot[GLOBAL GROUP])&&ISFILTERED(Pilot[GROUP]),//check if isfiltered global group and group at same time
		RANKX(ALL(Pilot[CLIENT]),[Invest Pilot BRL],,0,Dense),//client
		RANKX(ALLSELECTED(Pilot[CLIENT]),[Invest Pilot BRL],,0,Dense))//global
		)

Regards,

Xiaoxin Sheng

[bajimmy1983]

@v-shex-msft. Hi, how are you? I am always late replying you, sorry.

So, thanks a lot for your commitment. I appreciate it very much. 

I really cannot understand what is going on, because I am using same measure as yours, same database, same filters and my result are different.  

See what I get:

Result from 24, July, 2017

Anyway, thanks a lot again and I will try to find the gap, but I cannot figure it out now.

Best regards, 

Jimmy