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

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:

      • amotto11's avatar
        amotto11
        Helper II
        9 years ago

        So i got my count formula for all the ID's to be this

        Total Count = COUNTX(FILTER(ALLSELECTED(Table1[ID]),NOT(ISBLANK([Total_Amount]))),[Total_Amount])

         

        Do you know how i can count the number ranked 1, ranked 2, etc?

         

        I have to imagine it is something similar to my total count formula, I just can't do the if ranked 1 etc portion.

         

        It will look something like my last table in my original post. I am posting my total count formula since my data that i provided is a little different than my source data, so hopefully that can help you.