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

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

Hi vivran22  
Thank you for your help. I am still confuse about

VAR _CurrentValue =
    MAX ( SalesTable[Count] )

As I am unclear how we are calculating the count before we take median.

New try 1 = 
VAR _CurrentMonth =
    MAX ( ClaimsConvertedData[PROCDATE] ) 

VAR _CurrentValue =
    MAX ( ClaimsConvertedData[PROCDATE] )

VAR _EndMonth =
    EOMONTH ( _CurrentMonth, -2 ) + 1
VAR _Filter =
    DATESINPERIOD ( ClaimsConvertedData[PROCDATE], _EndMonth, -3, MONTH )

    VAR _Last3Month =
calculate (COUNT(ClaimsConvertedData[ID]),_Filter)
    return _Last3Month

it calculated the count of 2169. for July, August and Spetember should be 1023+455+816=2294 And I am still not sure how to send those values to median.

I am sorry if its silly question but your help is greatly appriciated.

vivran22's avatar
vivran22
Icon for Community Champion rankCommunity Champion
5 years ago

mukhan169 

 

Can you share the sample data/pbix file?

 

My inputs with comments on the measure below:

New try 1 = 
//Identifies the current month in the filter context
VAR _CurrentMonth =
    MAX ( ClaimsConvertedData[PROCDATE] ) 

//Identifies the current value in the filter context
VAR _CurrentValue =
    MAX ( ClaimsConvertedData[PROCDATE] )

//Identifies the Previous month in the filter context
VAR _EndMonth =
    EOMONTH ( _CurrentMonth, -2 ) + 1

//Gives the table with all the dates in last 3 months (excluding current month)
VAR _Filter =
    DATESINPERIOD ( ClaimsConvertedData[PROCDATE], _EndMonth, -3, MONTH )


VAR _Last3Month =
calculate (COUNT(ClaimsConvertedData[ID]),_Filter)
    return _Last3Month

 

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter