Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Solved! Go to Solution.
Second try...
Median of Count = calculate(MEDIANX('eurofxref-hist';[CountOfRows]) ; all('eurofxref-hist'))
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,
@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.
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?
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'))
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,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
97 | |
80 | |
67 | |
63 |
User | Count |
---|---|
147 | |
110 | |
108 | |
85 | |
64 |