DAX in Excel - MEDIANX not working to provide a true median in Pivot

[Nathag]

I have the following DAX code in a data model pivot in order to provide a calculated field for median amount based data that needs to be summed before taking the median.
The median needs to appear based on specific categories of data.

=MEDIANX (
SUMMARIZE (
Range,
Range[INCOME_RANGE], Range[EMPLOYEE], "AMOUNT",
SUM ( Range[AMOUNT] )
),
[AMOUNT]
)

The problem is that in columns that have an even number of summed values, MEDIANX is only grabbing the first value rather than taking the average of the two middle values as would be typical in the normal way of calculating median.

For example, I have 40 rows after the summarize does its thing and sums the amount field. Row 20 is 9,945 and row 21 is 10,783.50. A normal Excel median formula would average those middle two out of the 40 rows to bring back 10,364.25, however, MEDIANX is merely grabbing the first of the two middle values to return 9,945 without doing the extra work. No, I do not have blank values.

What is up with that?? is there a way to fix this problem in DAX since I cannot use the regular MEDIAN formula due to the too many arguments error? There has to be a way to get a true median in a pivot table based on multiple sorting criteria.

 

[Nathag]

An update. If I use =MEDIANX(Range1, [AMT TEST]) with the rows already summed by employee in a separate sheet, then it provides the correct median of 10,364.25 in a data model pivot using the DAX for a calculated measure.

The problem it seems is with my SUMMARIZE expression within MEDIANX where for some reason when it is summing the data and then running median, a row is either getting dropped or the formula isn't counting it properly.

 

[Nathag]

BTW - I am using DAX in an Excel Pivot, not in PowerBI. Not sure why mods moved the thread except perhaps because no one uses DAX in Excel. :P