Skip to main content
cancel
Showing results for 
Search instead 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

Reply
superhayan
Helper I
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.

 

superhayan_0-1718140442292.png

 

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
vicky_
Super User
Super User

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

vicky__0-1718153626081.png

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)

 

View solution in original post

2 REPLIES 2
vicky_
Super User
Super User

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

vicky__0-1718153626081.png

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)

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors