Solved: Re: Incorrect Mean and Median Values using DAX

POSPOS · ‎10-23-2023

Hi,

I have a requirement to calculate mean and median between the two dates. Please find sample data below:

Protocol	Submit Date	Intake Date	New calcualted column for datediff
190	04/05/2019	04/03/2019	2
191	07/01/2019	06/04/2019	27
201	07/05/2022	06/30/2022	5
204	08/02/2022	06/14/2022	49

I have created a new calculated column to calculate the datediff between the two dates using DATEDIFF.

Then I have created a new measure to calculate the mean of the days:

Average(New calcualted column for datediff)

This is giving 17 as result intead of 20.75 (83/4)

Median

Median(New calculated column for datediff)

This is giving 5 as result intead of 16.

Could you please suggest on how to fix this using DAX.

Thank you

HotChilli · ‎10-23-2023

I think you should be a bit careful with that table visual. It doesn't have any aggregations so in a way it's a kind of false table because the 190 row actually represents 2 rows which no-one would know unless they looked at the whole data.

Because of this 'false' table it will take a measure like this to get the average you want

MeasureX = var _summTab = SUMMARIZECOLUMNS('data1111 (2)'[Protocol Number], 'data1111 (2)'[Intake Date], data1111[First_SubmitDate], "MinDiff", MIN(data1111[Datediff]))
var _RowCount = COUNTROWS(_summTab)
var _SumTotal = SUMX(_summTab, [MinDiff])
RETURN
DIVIDE(_SumTotal, _RowCount)

View solution in original post

HotChilli · ‎10-24-2023

Similar code but use MEDIANX using the table and the mindiff

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

HotChilli · ‎10-23-2023

I think you should be a bit careful with that table visual. It doesn't have any aggregations so in a way it's a kind of false table because the 190 row actually represents 2 rows which no-one would know unless they looked at the whole data.

Because of this 'false' table it will take a measure like this to get the average you want

MeasureX = var _summTab = SUMMARIZECOLUMNS('data1111 (2)'[Protocol Number], 'data1111 (2)'[Intake Date], data1111[First_SubmitDate], "MinDiff", MIN(data1111[Datediff]))
var _RowCount = COUNTROWS(_summTab)
var _SumTotal = SUMX(_summTab, [MinDiff])
RETURN
DIVIDE(_SumTotal, _RowCount)

POSPOS · ‎10-24-2023

@HotChilli - I am now getting accurate results for average.
I tried to use the same code to derive median. Could you also help with that?

HotChilli · ‎10-23-2023

Thanks for posting pbix. This would be unsolveable without it.

Here is data in pbix:

average = 85/5 = 17

POSPOS · ‎10-23-2023

@HotChilli
Thanks for pointing this out.
As I am picking the first date whenever there are more than one dates for a protocol, (this is attached in the pbix),is there a way I can get the desired results:
Average = 83/4 as 20.75
Median = 16