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

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:

       

       

      • dkay84_PowerBI's avatar
        dkay84_PowerBI
        Microsoft Employee
        9 years ago

        I just noticed the very last ask you had for the rank to be dynamic based on the filter context, so this solution will not work for that.

         

        After looking at the DAX rank measure provided by others in this thread, it does not appear to dynamically update either.