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)
ViralPatel212's avatar
ViralPatel212
Icon for Resolver I rankResolver I
4 months ago

hello lbendlin  these are the slicers that i used: 

i tried the dax but my card show (Blank) 

Dealer Ranking KPI =


VAR _group = SELECTEDVALUE('Dealer Ranking'[Participant Group])
var a =
CALCULATETABLE(
    'Dealer Ranking', 'Dealer Ranking'[Participant Group] = _group,
    REMOVEFILTERS('Dealer Ranking'[Participant]))
RETURN
RANK(DENSE, a,ORDERBY([Commission 1],DESC,[Participant]))

 

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
4 months ago

The Participant slicer needs to be fed from a disconnected table.

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

    could you share the file where you created it? as its not working for me?

     

    thanks