cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Add Calculated Column of Median of Multiple Values of Each Row

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!

1 ACCEPTED SOLUTION
Super User

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

2 REPLIES 2
Super User

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

Helper I

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