Forum Discussion

POSPOS's avatar
POSPOS
Icon for Post Partisan rankPost Partisan
2 years ago
Solved

Incorrect Mean and Median Values using DAX

Hi,

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

ProtocolSubmit DateIntake DateNew calcualted column for datediff
19004/05/201904/03/20192
19107/01/201906/04/201927
20107/05/202206/30/20225
20408/02/202206/14/202249

 

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

Need Help
  • HotChilli's avatar
    HotChilli
    2 years ago

    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)

     

7 Replies

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    2 years ago

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

    Here is data in pbix:

    average = 85/5 = 17

    • POSPOS's avatar
      POSPOS
      Icon for Post Partisan rankPost Partisan
      2 years ago

      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

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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's avatar
      POSPOS
      Icon for Post Partisan rankPost Partisan
      2 years ago

      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?