cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DavidGolden
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. 
 
Could you please help me find the mistake in the expression? Thanks in advance!
1 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])





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
DavidGolden
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.
ValtteriN
Super User
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!






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors