incorrect result position on filtered ranking

[DavidGolden]

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. 

Could you please help me find the mistake in the expression? Thanks in advance!

[DavidGolden]

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.

[ValtteriN]

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!

[DavidGolden]

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.

[ValtteriN · Accepted solution]

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