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)
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
4 months ago

is the KPI card impacted by an independent slicers?  You will need to strategically place a REMOVEFILTERS in your measure for the RANK to have an actual table to work with.

 

 

 

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.