Forum Discussion
Using DAX RANKX to rank Aggregated totals (Newb)
It's a payroll table over a period of time. The address is an employee number. I summarized by employee number in a dashboard pane i would like the rank of the summarized monthly totals being calculated. I want the rank so i can filter the top ten results.
MainTable:
C_Address Week Hours Rate C_Gross
1234 Wk1 40 10 400
1234 Wk2 32 11 352
4321 Wk1 40 15 600
4321 Wk1 15 30 450
4321 Wk2 10 15 150
...
Summary table in dashboard that I would like to include the rank of the total value
C_Address C_Gross RankGross
1234 752 2
4321 1200 1
...
My example above has only 2 items. My data has 1000's I would like to view only the top 10 hence I am trying to use RankX. The ranks I show in the example above is what I would like to get. Currently, I am getting the rank as compared to the original individual values, so I dont get to rank 2 until the total falls below 600 in this example. Aternatively I get ranks as compared to the value itself producing a 1 for every line.
Hopefully that is enough Info.
I will have to wait now until next Tuesday to test your other suggestions. I am doing this on the side to learn how to better present some of my other analytics work. I had to do other work after I posted my query. Thanks for your responses.
Did you ever get an answer? I have the same issue
Wasn't involved in the initial discussion but this looked fun so I gave it a stab, here's the formula I used:
Rank = SELECTCOLUMNS(FILTER(SELECTCOLUMNS( SUMMARIZE(ALL(Table1), Table1[C_Address], "C_Gross", SUM(Table1[C_Gross])), "Address", Table1[C_Address], "Rank", RANKX(SUMMARIZE(ALL(Table1), Table1[C_Address], "C_Gross", SUM(Table1[C_Gross])), [C_Gross])), [Address] = Table1[C_Address]), "r", [Rank])
Very messy but it did give the expected result (I think):
