how to get the median without using table measure

[Anonymous]

Hi expert,

here is the data looks like:

id value

1     3

2     10

1      2

3       6

...

so  there are multiple rows to a certain id,

how can I get the median value and the median id without creating a new table such as using smmzrize?

thank you.

[suparnababu8]

Hi @Anonymous 

You can set median for any value with out using Dax measure by using default options in Power BI. Can you expand the median of below image.

and

Now here you can add your two colums in add line option.

Thanks!

[Anonymous]

Hi @Anonymous 

Agree with Idrissshatila. Using the MEDIAN function is a straightforward way to find the median value.

Allow me to add the method of calculating the median ID here, I hope it helps.

MedianID = 
VAR TotalRows = COUNTROWS(ALL(yourtable))
VAR MidRow = INT((TotalRows + 1) / 2)
RETURN
MAXX(
    TOPN(
        MidRow,
        ALL(yourtable),
        yourtable[value], ASC,
        yourtable[id], ASC
    ),
    yourtable[id]
)

It should be added that this result is accurate when you have an odd number of data rows, and when you have an even number of rows it returns the ID of the first of the two rows where the median is found.

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Uzi2019]

Hi @Anonymous 

please share the sample data and expected output as well. it will help us to understand the requirement more easily.. 

[Idrissshatila]

Hello @Anonymous ,

you create a measure as the following

Median = MEDIAN(<column>)

https://learn.microsoft.com/en-us/dax/median-function-dax