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.

 

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

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

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

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