Median Calculation Help - Need to not have key in my table

[tvalente]

Median calculation help - I get the right value if the dimension is in the table

3m ago

Hi All,

I want to return the median of some values. But in this instance, I don't want my dimension to appear in my table. This is because I want to create a scatter plot later on.

My Median calculation:

Median = 

CALCULATE(

MEDIAN(Reward),

ALLEXCEPT('Data', 'Data'[Key2])

)

Key2 is a calculated column:

Key2 = CONCATENATE([Career Level],[Location])

If my data looks like this with key2 present (I get the answer I want)

Employee Name	Key2	Reward	Median
Agatha	W4BAH	42,000	33,000
Timothy	W4BAH	24,000	33,000
Bob	W4RAK	220,000	222,500
Greg	W4RAK	225,000	222,500

What I want to do is have this:

Employee Name	Reward	Median
Agatha	42,000	33,000
Timothy	24,000	33,000
Bob	220,000	222,500
Greg	225,000	222,500

But if I remove KEY2 and replace with Career (Career is what I want in my scatter plot legend). My answer becomes this, which is wrong:

Employee Name	Career	Reward	Median
Agatha	W4	42,000	200,000
Timothy	W4	24,000	200,000
Bob	W4	220,000	200,000
Greg	W4	225,000	200,000

Any ideas?

Thanks all

Tiago

[tvalente]

Thank you 🙂

That worked a treat

[Anonymous · Accepted solution]

Hi @tvalente ,

Please try this measure.

Median = CALCULATE(MEDIAN('Data'[Reward]),ALL('Data'[Employee Name]),VALUES('Data'[Key2]))

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum