Add Calculated Column of Median of Multiple Values of Each Row

[superhayan]

Hello all, I have a seemingly simple question but I don't know how to get the right DAX formula.

Below is my sample data. I have columns A-D and now need to add a calculated column to calculate the median of col B, C, D of each row, while the values in these 3 columns must not be 0.

An excel-equivalent formula of cell E2 will be =MEDIAN(FILTER(B2:D2,B2:D2<>0))

What is the DAX formula I should put in the calculatd colum without needing to unpivot the columns BCD? Thank you!

[vicky_ · Accepted solution]

The best solution would be to change your table structure. I went back into powerquery and unpivoted to get this:

Best practice is to not use a calculated column unless you need to. If you must, then the following DAX should work:

median = CONVERT(MEDIANX(
    CALCULATETABLE('Table', ALLEXCEPT('Table', 'Table'[Product])), 
    'Table'[Price]
), INTEGER)

Note: the convert is necessary

If you can't change your data for whatever reason, then the following should give the same result:

median = CONVERT(MEDIANX(
    {'Table'[Shop A], 'Table'[Shop B], 'Table'[Shop C]}, 
    [Value]
), INTEGER)

[superhayan]

As I have other columns in the table and have few meausres that need to count row, I don't want to change the table structure.

For the last method you mentioned, can I know what is [value]?