Forum Discussion
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 :)
- Anonymous9 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
- AnonymousNot 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
- bajimmy1983Advocate 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)
- AnonymousNot 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