Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Median of count of rows

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,

 

2 ACCEPTED SOLUTIONS

Second try...

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

 

Cheers, Edgar Walther
ITsmart BI and Analytics consultant

View solution in original post

Anonymous
Not applicable

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,

View solution in original post

6 REPLIES 6
Eric_Zhang
Employee
Employee

@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
Not applicable

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,

Hi Ilocans,

 

I downloaded your sample and created this measure:

 

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

 

Is that what you mean?

 

Cheers, Edgar Walther
ITsmart BI and Analytics consultant
Anonymous
Not applicable

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,

Second try...

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

 

Cheers, Edgar Walther
ITsmart BI and Analytics consultant
Anonymous
Not applicable

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,

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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