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])

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi @mukhan169,

 

According to your description, I created some data to show:

 

 

  1. Create calculation table

 

sum by month = 
SUMMARIZE('Table','Table'[month],"sum by month",SUM('Table'[salemount]))

 

 

  1. Create three measures

 

Mdeiam = CALCULATE(
MEDIAN('sum by month'[sum by month]),
FILTER('sum by month',
'sum by month'[month]>=MAXX(ALL('sum by month'),[month])-3
&&'sum by month'[month]<=MAXX(ALL('sum by month'),[month])-1))

 

 

 

_last_month_sum = 
CALCULATE(
SUM('sum by month'[sum by month]),
FILTER('sum by month',
'sum by month'[month]=MAXX(ALL('sum by month'),[month])))

 

 

 

compare = 
IF(
'sum by month'[_last_month_sum]>'sum by month'[Mdeiam],
TRUE(),
FALSE()
)

 

 

  1. Result

 

You can downloaded PBIX file from here.

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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])