Power Pivot measurable or Calculated column help

[iggydarsa]

Hi all,

I have a power pivot table and the query is called "MyQuery"

I have the following fields:

UserName which is Text
CurrentYear which 0 or 1
WorkDone which is 0 or 1
ConstantFlag which is always 1


And lets say the dataset looks like this:

UserName | CurrentYear | WorkDone | ConstantFlag
Alice, 1, 1, 1
Alice, 0, 1, 1
Alice, 1, 1, 1
Bob, 1, 1, 1
Bob 1, 0, 1



I'd like to Rank the UserName (Alice and Bob) based on WorkDone/ConstantFlag where CurrentYear is 1.

So, because Alice have 2/2 makes 100% and Bob have 1/2 makes 50%, I want to see Alice's rank as 1 and Bob's rank as 2.

I tried measurables and calculated fields. I get either an #ERROR or a weird number

How can I accomplish this? Any help is appreciated.

 

[GraH]

Maybe this is a start for you?