RANKX Issues - Beginner woes

[Anonymous]

Hoping to get some help here with RANKX.  I have scoured the boards and tried almost all suggestions to no avail trying to get RANKX to work properly. Below is my scenario.

1.  created a measure 'Total Sales' [

Total Sales = SUMX('dim-INVOICE','dim-INVOICE'[SalesAmt])]

2. Applied RANKX to the total sales measure to rank it by value and the image below is what i get which is obviously wrong. [

Rank = RANKX(ALL('dim-INVOICE'), [Total Sales])

] I am at a loss of what is going wrong here.  TAll fields are in the same table.

[amitchandak]

Are you looking to rank only sales? Please refer these three articles 

Refer

https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Anonymous]

I was able to go through the articles and I decided to try sample data that I generated.  I am still getting the same issues.  I have included the pbix file i am using in hopes anyone can help glean anything wrong that I am doing.  Any assistance will be appreciated.

Thank you.

RankingProblem.PBIX 

[v-lid-msft]

Hi @Anonymous ,

We can try to use the following measures to meet your requirement:

Rank_SUM = RANKX(ALLSELECTED(RankingTest), [TotalSales_SUM],,ASC,Dense)

Rank_SUMX = RANKX(ALLSELECTED(RankingTest), [TotalSales_SUMX],,ASC,Dense)

Rank_CASUM = RANKX(ALLSELECTED(RankingTest), [TotalSales_CASUM],,ASC,Dense)

Rank_CASUMX = RANKX(ALLSELECTED(RankingTest), [TotalSales_CASUMX],,ASC,Dense)

If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that you have shared.

Best regards,

[Anonymous]

Thank you very much.

If you are look at the bottom of your table, rank 47 is appearing twice and the total sales value is different so someting tells me it is ranking something else and not the total sales value.  At any rate I believe the highest rank will be 33.

What I am hoping for is a proper ranking of the total sales as depicted in the attached images.

top of tablebottom of table

[v-lid-msft]

Hi @Anonymous ,

Sorry for our late reply, we can use the following measures to meet your requirement:

Rank_SUM = 
RANKX (
    CALCULATETABLE (
        DISTINCT ( 'RankingTest'[ItemNumber] ),
        ALLSELECTED ( 'RankingTest' )
    ),
    CALCULATE ( [TotalSales_SUM] ),
    ,
    ASC,
    DENSE
)

Rank_SUMX = 
RANKX (
    CALCULATETABLE (
        DISTINCT ( 'RankingTest'[ItemNumber] ),
        ALLSELECTED ( 'RankingTest' )
    ),
    CALCULATE ( [TotalSales_SUMX] ),
    ,
    ASC,
    DENSE
)

Rank_CASUM = RANKX (
    CALCULATETABLE (
        DISTINCT ( 'RankingTest'[ItemNumber] ),
        ALLSELECTED ( 'RankingTest' )
    ),
    CALCULATE ( [TotalSales_CASUM] ),
    ,
    ASC,
    DENSE
)

Rank_CASUMX = 
RANKX (
    CALCULATETABLE (
        DISTINCT ( 'RankingTest'[ItemNumber] ),
        ALLSELECTED ( 'RankingTest' )
    ),
    CALCULATE ( [TotalSales_CASUMX] ),
    ,
    ASC,
    DENSE
)

Best regards,

[Anonymous]

Thank you for the response.  

Unfortunately, it is not working in my powerBI file. 

Possibly my file is now corrupted!  I have used the function in a different situation and it worked however in this instance it is not working.  I settled for using the RANK.EQ alternative, only it added a few more steps to get to the result I wanted.

[Anonymous]

Thank you.  I will review the articles in hopes to shed some light.

[kentyler]

So, looking at the total sales measure... each item has multiple invoices...and your summing them up ?

[Anonymous]

correct!