Forum Discussion

amotto11's avatar
amotto11
Helper II
9 years ago

Ranking

I am new to Power BI and Dax all together and am trying to learn.   I have a table with 3 columns: ID, Company, Amount. I am trying to rank the amount from the company by ID. I believe i need a mea...
amotto11's avatar
amotto11
Helper II
9 years ago

I was able to get to my second table shown, the ranking table. I used two measures.

 

Total_Amount= sum(Table1[Amount])

Rank = IF(ISBLANK([Total_Amount]),BLANK(),RANKX(FILTER(ALLSELECTED(Table1[Company]),NOT(ISBLANK([Total_Amount]))),[Total_Amount],,1,Dense))

 

Now I am trying to reach my final table, the one counting the rankings. Any help on this or reviewing my previous formula would be appretiated. Thanks

  • amotto11's avatar
    amotto11
    Helper II
    9 years ago

    I am new to Power BI and Dax all together and am trying to learn.

     

    I have a table with 3 columns: ID, Company, Amount. I am trying to rank the amount from the company by ID. I believe i need a measure of Total_Amount and Rank, but cannot seem to figure out the RankX requirements and how it works.

     

    I have created a measure of Total_Amount = sum(Table1[Amount])

     

    Here is a sample of the raw data:

     

  • Sean's avatar
    Sean
    Community Champion
    9 years ago

    amotto11

     

    Ranks ASC =
IF (
    HASONEVALUE ( 'Table1'[Company] ),
    RANKX (
        ALL ( 'Table1'[Company] ),
        CALCULATE (
            SUM ( Table1[Amount] ),
            ALLEXCEPT ( 'Table1', 'Table1'[ID], 'Table1'[Company] )
        ),
        ,
        ASC
    )
)

     

    amotto11Here's what I get with your formula

     

    • amotto11's avatar
      amotto11
      Helper II
      9 years ago

      Well i am making a sample set that i thought was similar to my data, but apparently not. When i recreated the sample set i get what you are experiencing. In my original data, my formula is working, but yours does not have a rank of 1, it is starting with rank 2.

      • Sean's avatar
        Sean
        Community Champion
        9 years ago

        As long as you got it working -that's what matters! :smileyhappy: