cancel
Showing results for
Did you mean:
Frequent Visitor

## incorrect result position on filtered ranking

Hello, I have recently started to use PowerBI.

We have data for sales within the (Emi_Todas) table, and a related table of sellers (PAS), wich contains data for each seller.

The sellers table (PAS), whose primary key is MPROD has 2 different subgroups: Group 1: MPROD<45000 and Group2 MPROD>=45000.

I´m currently trying to show the position in the ranking for a specific seller (MPROD) according to total sales [Prima], making a difference whether both groups of sellers are considered or only the first Group is considered.

So, I created two different meassures:

RankingIntermed = RANKX(all(PAS),SUMX(RELATEDTABLE(Emi_Todas),[Prima]))
RankingPAS2 = RANKX(filter(all(PAS),PAS[MPROD]<45000),SUMX(RELATEDTABLE(Emi_Todas),[Prima]),,DESC,Skip)
When I pick a seller from the first group, [MPROD] < 45000, both measseres work fine.
The problem is that when I pick a seller from the second group, [MPROD] >= 45000, RankingPAS2 should result blank, since the meassure should only consider the filtered table [MPROD] < 45000, but instead it returns the value of last position in the overall ranking.

1 ACCEPTED SOLUTION
Super User

@DavidGolden

Maybe a different approach then? If you want to return blank when the [MPROD] >= 45000 you can include this in the measure using IF. So IF([MPROD] >= 45000,BLANK(),[Ranking measure])

Proud to be a Super User!

4 REPLIES 4
Frequent Visitor

That´s right.

Since the idea is to insert a slicer in order to chose among sellers, then I had to include MAX/MIN for IF function to work.

RankingPAS = IF(MAX(PAS[MPROD])<45000,RANKX(all(PAS),calculate(SUMX(RELATEDTABLE(Emi_Todas),[Prima]),PAS[MPROD]<45000),,DESC,Skip),BLANK())
Thanks a lot.
Super User

Hi,

Instead of filtering the PAS table you should focus on chaning the measure logic. Example,
Here I only want to rank products with Category "B" :

RANKX(all('Table (7)'),calculate(SUM('Table (7)'[sales]),'Table (7)'[Category 1]="B"),,DESC)

So place the SUMX in CALCULATE and apply the filter there.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

Proud to be a Super User!

Frequent Visitor

Hi @ValtteriN , thanks for the advice. I applied your aproach the the syntax. However, the result is still the same as before.

When chosing a member from a different category, the returned value is not blank, but the last position in the ranking, wich is incorrect.

Super User

@DavidGolden

Maybe a different approach then? If you want to return blank when the [MPROD] >= 45000 you can include this in the measure using IF. So IF([MPROD] >= 45000,BLANK(),[Ranking measure])

Proud to be a Super User!

Announcements