Forum Discussion
mukhan169
5 years agoHelper III
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...
- 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])
vivran22
5 years agoCommunity Champion
mukhan169 I have sent you access request for the file.
Cheers!
Vivek
Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter