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

hi lbendlin 

Where would i place it? 

this is what my goal is: 

The KPI card shows the rank of the currently selected participant's dealer against all other participants in the same Participant Group. It answers: "How well does my dealer rank within my group?"

Card Setup:

Filters on the card:

  • Participant Group = [From page slicer]
  • Dealer = [From page slicer]
  • NOTE: NO filter on Participant by default there page will be filtered to client 2

Values:

  • The ranking measure that compares the current participant's commission against all others in the group

How it should work: When a user (Client 2) views the report:

  • Page filter shows: Participant Group = "Group A", Dealer = "Dealer 107"
  • The slicer shows they are viewing as "Client 2"
  • The KPI should rank Client 2's commission against Client 1's commission within Group A
  • Result: Rank 2 of 2

The issue is that the measure needs to evaluate "Client 2's commission vs all participants in Group A" without the Participant slicer restricting what participants are visible to the calculation.

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
4 months ago
the currently selected participant

see my edited reply.  You will need to explain how you do that in the UI.

  • 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