title: PowerPivot Ranking Groups using DAX's Rankx - Ranking Using Sum of a Field
body: <p>Am trying to rank groups by summing a field (not a calculated column) for each group so I get a static answer for each row in my table.</p>
  
  <p>For example, I may have a table with state, agent, and sales.  Sales is a field, not a measure.  There can be many agents within a state, so there are many rows for each individual state.  I am trying to rank the states by total sales within each state. </p>
  
  <p>I have tried many things, but the ones that make the most sense to me are:</p>
  
  <pre><code>rankx(CALCULATETABLE(Table,allexcept(Table,Table[AGENT]),sum([Sales]),,DESC)
  </code></pre>
  
  <p>and </p>
  
  <pre><code>=rankx(SUMMARIZE(State,Table[State],"Sales",sum(Table[Sales])),[Sales])
  </code></pre>
  
  <p>The first one is creating a table where it sums sales without grouping by Agent. and then tries to rank based on that.  I get #ERROR on this one.</p>
  
  <p>The second one creates a table using SUMMARIZE with only sum of Sales grouped by state, then tries to take that table and rank the states based on Sales.  For this one I get a rank of 1 for every row.</p>
  
  <p>I think, but am not sure, that my problem is coming from the sales being a static field and not a calculated measure.  I can't figure out where to go from here.  Any help?</p>