Median giving incorrect values

[stevezly]

Hi All, 

I am trying to calculate median over some grouped data, but the "median" value I get back is the same for each group which cannot be correct. Am I missing something here?

Thanks

SummTable =

ADDCOLUMNS(

SUMMARIZE(pack_et,

pack_stimuli[stimuli_country],

pack_stimuli[stimuli_category],

pack_et[zone],

pack_projects[year],

pack_stimuli[stimuli_region],

pack_stimuli[stimuli_design]), "Median", MEDIAN(pack_et[avg(seen)]

)

)

[johnt75 · Accepted solution]

It would be returning the median across the entire dataset, it wouldn't be split into any categories.

[stevezly]

Thanks John.

Is there anyway I could get these to match up do you think?

Basically I'm having to use a summary column in order to bypass RLS and create benchmarks, but I can't think of a way to calculate median in a way that would tie up with the rest of the report.

Thanks for your help so far

[johnt75]

If you add the CALCULATE then the summary table should be correct, with the correct median per category.

[stevezly]

OK,

There was a report level filter on that i needed to add into the Calculate, and now the numbers matched up.

You've helped so much already but I do have one more question. I need this summary calc to be filterable in the report. I did have this working by adding filters into a new measure that looked something like this.

IF(ISFILTERED(pack_stimuli[stimuli_region]), SummTable[stimuli_region] = SELECTEDVALUE(pack_stimuli[stimuli_region]), SummTable[stimuli_region] in VALUES(SummTable[stimuli_region]))

However this measure was essentially calculating the median of the median so would not give relevant answers. Would i be able to put filters such as this or similar into the summarized calculated column to make the report slicers functional?

Thanks so much again

[johnt75]

Calculated tables and columns are only calculated during data refresh so they do not take into account filters or slicers. The only way to get it to react to slicers would be by creating a measure which stores the table in a variable, thus calculating it each time, and then retrieving the relevant value or aggregate from that variable.

You can use functions like FILTER and SELECTCOLUMNS on variables, just like normal tables.

[stevezly]

Thanks John, I actually tried this the other day but unfortunately the RLS wasnt working with it. I'll take another look now but I'll leave the DAX here incase you can notice anything that might be causing it.

Thanks a lot for all help

NewMeasure =

VAR VarTable =

ADDCOLUMNS(

SUMMARIZE (

CALCULATETABLE(

pack_et,

pack_et[time_stamp] = 5,

IF(ISFILTERED(pack_stimuli[stimuli_region]), SummTable[stimuli_region] = SELECTEDVALUE(pack_stimuli[stimuli_region]), SummTable[stimuli_region] in VALUES(SummTable[stimuli_region])),

IF(ISFILTERED(pack_stimuli[stimuli_category]), SummTable[stimuli_category] = SELECTEDVALUE(pack_stimuli[stimuli_category]), SummTable[stimuli_category] in VALUES(SummTable[stimuli_category])),

IF(ISFILTERED(pack_projects[year]), SummTable[year] = SELECTEDVALUE(pack_projects[year]), SummTable[year] in VALUES(SummTable[year])),

IF(ISFILTERED(pack_stimuli[stimuli_design]), SummTable[stimuli_design] = SELECTEDVALUE(pack_stimuli[stimuli_design]), SummTable[stimuli_design] in VALUES(SummTable[stimuli_design]))

),

pack_stimuli[stimuli_country], pack_stimuli[stimuli_category], pack_et[zone],

pack_projects[year],

pack_stimuli[stimuli_region],

pack_stimuli[stimuli_design]),

"Median", MEDIAN(pack_et[avg(seen)]

))

RETURN

MEDIANX ( VarTable, [Median])

[stevezly]

Simplified but same problem

NewMeasure =

VAR VarTable =

CALCULATETABLE(

ADDCOLUMNS(

SUMMARIZE (pack_et,

pack_stimuli[stimuli_country],

pack_stimuli[stimuli_category],

pack_et[zone],

pack_projects[year],

pack_stimuli[stimuli_region],

pack_stimuli[stimuli_design]),

"Median", MEDIAN(pack_et[avg(seen)]

)),

pack_et[time_stamp] = 5)

RETURN

MEDIANX ( VarTable, [Median])

[johnt75]

You could put any filtering logic inside the CALCULATETABLE function, but I'm not clear what you're trying to return.

[stevezly]

Ah, yes that's what I'm trying to acheieve with this summarized table.

No worries, thanks for your help.

[stevezly]

I just need the median from that summarized table returned so I can add it into existing matrix view as a benchmark. That code I linked works, and the filters are enabled, however when I turn on RLS, the figure also changes with the role. Unfortunately, that's why I'm using a summary table in the first place.

[johnt75]

Ah, I don't know enough about RLS to be able to help with that.

[stevezly]

I do find it strange though, that the RLS works fine when I use the summarize function in a calculated column, but it doesnt when i use it as a variable table.

[johnt75]

Are you sure that RLS is being applied to the calculated table? The table would only be calculated during data refresh, so I don't think that RLS would come into effect - there wouldn't be any logged in user at that point

[stevezly]

That's why I'm using the calculated table; to bypass the RLS filter. Although it seems like there is no solution to my problem as these calculations are incompatible with the slicer.

[johnt75]

You need to wrap the MEDIAN call inside CALCULATE() so that it picks up the row context from the SUMMARIZE.

[stevezly]

Ah thanks, was being silly. 

It is strange though. The values I am getting for "Median" do not match up with  spot checked values from the main Median measure, when i filter down into these groups.