CODEWITHSUNDEEP
powerbidax
daOnlyBG
daOnlyBG

Reputation: 601

Ranking a Measure Value (as opposed to a column value) in DAX?

Short version of question: how can I create a rank based on a calculated measure?

Question background: I have a table/query that yields many rows for each employee, with each row having a time taken value.

I'd like to calculate the average time taken for each employee, and then present a table with a row for each employee, showing their name, average time taken, and rank (based on time taken).

To do the first part, I created a measure called AverageTimeLength and set it equal to:

AverageTimeLength = Average(Table_name[Column_name])

Then I coded the following:

AverageTimeLength_employee = CALCULATE([AverageTimeLength], GROUPBY(Table_name, Table_name[EmployeeName]))

These two are measures; I was able to insert the second into the new table chart I'm creating, but unfortunately, I can't use RANKX() on it because the measure values don't come from a column. If I try to create a column derived from the measure (i.e., column_name = [AverageTimeLength_employee]) I just get an error accusing column_name of circular reasoning.

What I want to do seems like it should be simple; does anyone know how I can create a simple rank parameter, to rank the measure values?

Upvotes: 0

Views: 5564

Answers (1)

alejandro zuleta
alejandro zuleta

Reputation: 14108

You can create the Average measure and use it in the Rank measure as follows:

Average = AVERAGE([Time taken]) 


Rank = IF (
    HASONEVALUE ( Table_Name[Name] ), 
    RANKX ( ALL ( Table_Name[Name] ), [Average])
)

Hope it helps.

Upvotes: 2

Related Questions