Forum Discussion

DavidGolden's avatar
DavidGolden
Icon for Helper I rankHelper I
4 years ago
Solved

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!
RANKX issue
  • ValtteriN's avatar
    ValtteriN
    4 years ago

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

4 Replies

  • ValtteriN's avatar
    ValtteriN
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      DavidGolden
      Icon for Helper I rankHelper I
      4 years ago

      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's avatar
        ValtteriN
        Icon for Community Champion rankCommunity Champion
        4 years ago

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

  • DavidGolden's avatar
    DavidGolden
    Icon for Helper I rankHelper I
    4 years ago

    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.