Forum Discussion

bajimmy1983's avatar
bajimmy1983
Advocate V
9 years ago
Solved

Dynamic RANKS using CARDS elements

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 :)

  • Anonymous's avatar
    Anonymous
    9 years ago

    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

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    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's avatar
      bajimmy1983
      Advocate V

      Hi Anonymous, 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)

      • Anonymous's avatar
        Anonymous
        Not applicable

        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