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.

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])
stevezly's avatar
stevezly
Icon for Helper I rankHelper I
4 years ago
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's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago

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

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

    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's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago

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

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

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


    No worries, thanks for your help.

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

    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's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago

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

    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.