Forum Discussion

ViralPatel212's avatar
ViralPatel212
Icon for Resolver I rankResolver I
4 months ago
Solved

Ranking KPI card

I have a page with differnent slicers. in the example the table has been sliced with period = q3, Dealer = 107 and Participant = Client 2. the Ranking on the table works perfectly as its ranks Client...
  • ViralPatel212's avatar
    ViralPatel212
    4 months ago

    HI everyone

     

    Thanks for the suggestion!

     

    i was able to figure it out with this dax: 

     

    Dealer Ranking KPI = 
 
VAR _group = MAX('Dealer Ranking'[Participant Group])
VAR _dealer = MAX('Dealer Ranking'[Dealer])
VAR _participant = SELECTEDVALUE('Dealer Ranking'[Participant])
VAR _currentComm = 
    CALCULATE(
        SUM('Dealer Ranking'[Commission LC - Everyone]),
        'Dealer Ranking'[Participant] = _participant
    )
VAR _participants = 
    CALCULATETABLE(
        VALUES('Dealer Ranking'[Participant]),
        REMOVEFILTERS('Dealer Ranking'[Participant]),
        KEEPFILTERS('Dealer Ranking'[Participant Group] = _group),
        'Dealer Ranking'[Dealer] = _dealer
    )
VAR _withComm = 
    ADDCOLUMNS(
        _participants,
        "Comm", CALCULATE(SUM('Dealer Ranking'[Commission LC - Everyone]))
    )
RETURN
RANKX(_withComm, [Comm], _currentComm, 0)
MFelix's avatar
MFelix
Icon for Super User rankSuper User
4 months ago

Hi ViralPatel212 ,

 

Now I understand the concept that you are trying to give so in this case I believe that what you need is to recalculate the measure in order to get the correct context for that specific user inside the page filter so in this case remove the filter context of the Client that is currently seeing the report based on it's selection.

 

Not sure if the Group is coming from the same table has the participant but if it's from a different table just change your calculation for the card:

 

Dealer Rank (in Group) CARD VISUAL =
RANK(DENSE, ALL('Dealer Ranking'[Participant]), ORDERBY([Commission 1], DESC, [Participant]))

 

This should return the correct number for that specific calculation.

 

If this does not work can you please let me know what is the relation between the  Dealer and the Participation group are they part of the same table, are they in the dimension table or the fact table how is that calculating for the rank.

ViralPatel212's avatar
ViralPatel212
Icon for Resolver I rankResolver I
4 months ago

hello MFelix 

 

Afraid that did not work, just returned the number 3 where it should be 2 both dealer and participants are in the same table For example Group A has got Partipant 1,2 and Group B has got Participant 3 

 

 

 

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    4 months ago

    In terms of model how is the Group related wirth the participant?
    Are they part of the same table?

    What does your model look like, is it possible to send a sample file of your report.