Median of count of rows

[Anonymous]

Hi,

I am trying since a few days to create create a Median in DAX in Power Bi dekstop. I know a function exist but I can't achieve to make it work for me.

I have 1 table with information about doctor appointement and I need to compare the number of appointment of each doctor for a period with the median of his clinic.

So I have 3 slicer: Year, Quarter and Clinic and I created a line and "clustered column chart" with my doctors and the number of appointments: it is a is count of rows in my table (or I have also a column with 1 for each row and I make a sum of it).

Then I try to create a calculated measure for the Median like this:

Mediane = MedianX(Sheet1; CALCULATE(SUM(Sheet1[Value1])))

or

Mediane = MedianX(Sheet1; CALCULATE(COUNTROWS(Sheet1);ALL(Sheet1[Doctor]);ALLEXCEPT(Sheet1;Sheet1[Clinic Name];Sheet1[Year];Sheet1[Quarter]))

...

Nothing works.

Does anyone have an idea how I could make it work ?

Thank you for your help.

Regards,

[waltheed · Accepted solution]

Second try...

Median of Count = calculate(MEDIANX('eurofxref-hist';[CountOfRows]) ; all('eurofxref-hist'))

[Eric_Zhang]

@Anonymous

Your requirement is not clear for me. Could you upload some sample data and be more specific on the expected output? It would be better you can upload a sample pbix.

[Anonymous]

Hi Eric

Here a data sample not the same I talked about in my previous message: https://onedrive.live.com/redir?resid=17A7AFFEED8D650E!634&authkey=!AFMa8ygyM-znTPs&ithint=file%2cpb...

I want to create the measure that is the median for the number a rows for each year.

If you look at the first columns of table there is counts of rows. The median value is 255.5 if my calculation are correct. I want to be able to create a chart with the count of rows in bars and a line which represent the median, like the first chart.

I made it for a column in the first chart but I can't for a calculated measure or a count of rows.

If anyone can help me.

Thanks,

[waltheed]

Hi Ilocans,

I downloaded your sample and created this measure:

Median of Count = MEDIANX('eurofxref-hist';[CountOfRows])

Is that what you mean?

[Anonymous]

Hi,

I tested your solution but it gives the number of rows for each year:

1999 259

2000 255

....

If the median works it should return for each row 255.50

Regards,

[waltheed · Accepted solution]

Second try...

Median of Count = calculate(MEDIANX('eurofxref-hist';[CountOfRows]) ; all('eurofxref-hist'))

[Anonymous · Accepted solution]

Hi,

Thank you, it seems to work but I was excpected more a 255.5 than a 256.

I think I also found a solution but more complex:

Median of Count = CALCULATE(MEDIANX(SUMMARIZE('eurofxref-hist','eurofxref-hist'[Year],"Cnt",CALCULATE(COUNTROWS('eurofxref-hist'))), [Cnt]),ALL('eurofxref-hist'))

It gives the 255.5 but I don't understand why with the same median function whe have 2 different results.

Regards,