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

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?

      • dkay84_PowerBI's avatar
        dkay84_PowerBI
        Microsoft Employee
        9 years ago

        I took a very different approach to solving this.  I used the query editor to add the Rank by creating a custom function that would sort the value field in ascending order and then add an index (starting with 1).  Then, I grouped the original data table by ID, with the aggregation set to "All Rows".  Then, invoke this custom function as a new column and it will give you a rank column after expanding the table.

         

        Here is the function:

        let
    Source = (column) as table => let
        Source = column,
        #"Sorted Rows" = Table.Sort(Source,{{"Value", Order.Ascending}}),
        #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Rank", 1, 1)
    in
        #"Added Index"
in
    Source

        Then, using DAX, I created the following 3 measures:

        Rank1 = IF(CALCULATE(COUNT(Table1[Index]),Table1[Index]="1")=0,0,CALCULATE(COUNT(Table1[Index]),Table1[Index]="1"))
        Rank2 = IF(CALCULATE(COUNT(Table1[Index]),Table1[Index]="2")=0,0,CALCULATE(COUNT(Table1[Index]),Table1[Index]="2"))
        Total = [Rank1]+[Rank2]

        After putting these into a table with the Company field set to "Show Items With no Data" and "Do Not Summarize", you get the following: