topic Re: Add Calculated Column of Median of Multiple Values of Each Row in DAX Commands and Tips

Add Calculated Column of Median of Multiple Values of Each Row

superhayan — Tue, 11 Jun 2024 21:21:29 GMT

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!

Re: Add Calculated Column of Median of Multiple Values of Each Row

vicky_ — Wed, 12 Jun 2024 00:59:03 GMT

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)

Re: Add Calculated Column of Median of Multiple Values of Each Row

superhayan — Wed, 12 Jun 2024 01:11:31 GMT

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