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

[tvalente]

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

[v-shex-msft · Accepted solution]

HI @tvalente,

Did you mean to show the aggerated result values on chart without add correspond category fields? If that is the case, I'd like to suggest you use SUMMARIZE function to summary table records based on correspond category group in variable and use iterator function to aggerate calculated results.

Measure Totals, The Final Word 
Regards,

Xiaoxin Sheng