Forum Discussion

mukhan169's avatar
mukhan169
Helper 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])

mukhan169's avatar
mukhan169
Helper III
5 years ago

@v-yangliu-msft Thank you for your response. Doing a table is not possible as it has about 10 different slicers. and the total is dependent on what they selected and calculations needed to be done on the fly.

mukhan169's avatar
mukhan169
Helper III
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])