topic Re: Ranking KPI card in DAX Commands and Tips

Ranking KPI card

ViralPatel212 — Thu, 07 May 2026 11:16:22 GMT

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 2 within group A for Dealer 107 as 2. However i need to show that 2 in a KPI card. Tried to use that measure in a KPI but it returns as 1 always.

this is the dealer Rank (in group) measure:

Dealer Rank (in Group) =

RANK(DENSE, ALLSELECTED('Dealer Ranking'[Participant]), ORDERBY([Commission 1], DESC, [Participant]))

thanks

viral

Re: Ranking KPI card

ViralPatel212 — Thu, 07 May 2026 11:24:37 GMT

this is the dealer Rank (in group) measure:

Dealer Rank (in Group) =

RANK(DENSE, ALLSELECTED('Dealer Ranking'[Participant]), ORDERBY([Commission 1], DESC, [Participant]))

Re: Ranking KPI card

MFelix — Thu, 07 May 2026 11:40:53 GMT

Hi ViralPatel212 ,

That specific card you are refering has a filter on the specific customer?

Since you are using the ALLSELECTED it will only have impact on the participants on the query in this matrix you have Client 1 and Client 2 giving you the number 2, being on a card visual the allselected will have the number 1 since the context is different and you only get a single result.

Can you explain what is the intended purpose and how the card is setup in values and filters.

Re: Ranking KPI card

ViralPatel212 — Thu, 07 May 2026 11:48:54 GMT

Hello MFelix

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.

Re: Ranking KPI card

lbendlin — Thu, 07 May 2026 12:56:20 GMT

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.

Re: Ranking KPI card

ViralPatel212 — Thu, 07 May 2026 12:39:34 GMT

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.

Re: Ranking KPI card

lbendlin — Thu, 07 May 2026 12:59:23 GMT

the currently selected participant

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

Re: Ranking KPI card

ViralPatel212 — Thu, 07 May 2026 13:07:20 GMT

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]))

Re: Ranking KPI card

lbendlin — Thu, 07 May 2026 13:16:12 GMT

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

Re: Ranking KPI card

ViralPatel212 — Thu, 07 May 2026 13:22:08 GMT

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

thanks

Re: Ranking KPI card

lbendlin — Thu, 07 May 2026 13:55:03 GMT

see attached

Re: Ranking KPI card

MFelix — Thu, 07 May 2026 15:21:35 GMT

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.

Re: Ranking KPI card

ViralPatel212 — Thu, 07 May 2026 15:35:32 GMT

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

Re: Ranking KPI card

MFelix — Thu, 07 May 2026 15:58:56 GMT

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.

Re: Ranking KPI card

Ashish_Mathur — Thu, 07 May 2026 23:23:22 GMT

Hi,

Please share the download link of the PBI file with your measures already written and visuals already built. Show the problem there clearly.

Re: Ranking KPI card

cengizhanarslan — Fri, 08 May 2026 19:43:10 GMT

It would be easier if you have shared an example dataset but without that I gues it has to be something like this:

Dealer Rank (Card) = VAR _SelectedParticipant = SELECTEDVALUE ( 'Dealer Ranking'[Participant] ) VAR _SelectedGroup = SELECTEDVALUE ( 'Dealer Ranking'[Participant Group] ) VAR _RankTable = ADDCOLUMNS ( CALCULATETABLE ( VALUES ( 'Dealer Ranking'[Participant] ), ALLSELECTED ( 'Dealer Ranking'[Participant] ), 'Dealer Ranking'[Participant Group] = _SelectedGroup ), "@Comm", CALCULATE ( [Commission 1] ) ) RETURN COUNTROWS ( FILTER ( _RankTable, [@Comm] > CALCULATE ( [Commission 1] ) ) ) + 1

Re: Ranking KPI card

v-veshwara-msft — Mon, 11 May 2026 07:02:50 GMT

Hi ViralPatel212 ,

Thanks for reaching out to Microsoft Fabric Community.

Just checking in to see if your query is resolved and if any responses were helpful.
Otherwise, feel free to reach out for further assistance.

Also to assist better, could you share sample data or sample .pbix file without any sensitive information. You can upload your files to any cloud storage like OneDrive or GoogleDrive and paste the link here with appropriate permissions.

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Thank you.

Re: Ranking KPI card

ViralPatel212 — Mon, 11 May 2026 08:57:22 GMT

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)