hasonevalue--looking to build a ranking pivot table

[ThomasDay]

Good afternoon,

I am looking to build a simple ranking pivot table.  It will have rows that have a single hospital name, a column of a measure which is the total margin of the facility, and then I want a ranking of that margin for the selected hospitals.

Here's the pivot table and my stab at the DAX formula for Ranking.  The parts I had difficulty figuring were--does the HasOneValue Need to be from the same table as the Margin...The hospital name is the one side of a many-to-one of the file with the Total Profit Margin measure in it....so I think that works. 

S

• So the formula is attempting to define a ProfitMarginRank variable IF the context has one value (ie one hospital name)...and rank the row on the basis of Total Profit Margin.  
• The error message says there isn't only one value for the hospital Name...well, not until it's in the table, right?
• Perhaps there is a AllSelected or All required, but that part seems a little mysterious right now--would just like to get it to show something and then try to debug.

Any ideas?  Many thanks,

Tom

Thanks in advance,

Tom

[Mi2n]

I might be trying to over simply this, but, does this help?

ProfitMarginRank = Rankx(All(HOSPITAL10_PROVIDER_ID_INFO[Hospital_Name]),[Total Profit Margin])

[ThomasDay]

Will try right after dinner and a beer (!)  Thanks...Tom

[ThomasDay]

Yeah--that got me on the right track all right!  Thanks.  Here's the formula I got to work..

ProfitMarginRank = Rankx(ALLSELECTED(HOSPITAL10_PROVIDER_ID_INFO[Hospital_Name]),[Total Profit Margin]) .  

It's important that the Total Profit Margin is calculated at all--I filtered out blanks and will have to figure out how to handle them better.  Some don't report all data needed to compute.

Anyway, that was a big help.  Thank you ,

Tom