Forum Discussion

mukhan169's avatar
mukhan169
Icon for Helper III rankHelper III
5 years ago
Solved

Calculating Median on Measure

Good afternoon, I am stumped and your help will be greatly appriciated, I am trying to calculate median on the maximum Month from date in table which is October , so for October I want to count July...
Need Help
  • mukhan169's avatar
    mukhan169
    5 years ago

    Thank you all very much I ended up doing the following

    Created my 5 measure of last 5 months (As i needed 5 months count to calculate median)

    One = calculate (COUNT(ClaimsConvertedData[CustID]), DATESINPERIOD ( ClaimsConvertedData[PROCDATE], EOMONTH ( MAX ( ClaimsConvertedData[PROCDATE] ), -1 ), -1, MONTH ),FILTER(ClaimsConvertedData,ClaimsConvertedData[T&C Identifier]="Y"))
    Two = calculate (COUNT(ClaimsConvertedData[CustID]), DATESINPERIOD ( ClaimsConvertedData[PROCDATE], EOMONTH ( MAX ( ClaimsConvertedData[PROCDATE] ), -2 ), -1, MONTH ),FILTER(ClaimsConvertedData,ClaimsConvertedData[T&C Identifier]="Y"))

    and so on.

    The following is the measure that returns the median.

    median = MEDIANX(
Union(
    ROW("Measure", "Measure 1", "Measure Value",[One]),
    ROW("Measure", "Measure 2", "Measure Value",[Two]),
    ROW("Measure", "Measure 3", "Measure Value",[Three]),
    ROW("Measure", "Measure 4", "Measure Value",[Four]),
    ROW("Measure", "Measure 5", "Measure Value",[Five])
),[Measure Value])