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

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