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...
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.

      • amotto11's avatar
        amotto11
        Helper II
        9 years ago

        I think i got the Rank 1, and Rank 2 as seperate functions...

         

        Rank 1 Count = IF(ISBLANK(COUNTX(FILTER(ALLSELECTED(Table1[ID]),[Rank]=1),[Total_Amount])),0,COUNTX(FILTER(ALLSELECTED(Table1[ID]),[Rank]=1),[Total_Amount]))

         

        Is there a dynaminc function i can use, where i don't have to have 5 formulas for the top 5? I can just use one and filter on the top x that i want, or would i have to create another table with the numbers 1, 2, 3, 4... and go about it that way?