Forum Discussion

stevezly's avatar
stevezly
Icon for Helper I rankHelper I
4 years ago
Solved

Median giving incorrect values

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   ...
Need Help
  • johnt75's avatar
    johnt75
    4 years ago

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

johnt75's avatar
johnt75
Icon for Super User rankSuper User
4 years ago

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's avatar
stevezly
Icon for Helper I rankHelper I
4 years ago

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])