Median calculation of a Measure (Covariance)

[hzapiainr]

Hello! 

I’m having a problem which has troubled me for the last week and I’m been unable to get the result I need.

What I do is to determine a Measure (I need to make it dynamically as there are several slicers in the dashboard) to calculate the Covariance factor based on the accumulated revenues of each customer per Quarter of the last 12 months.

COV = 
VAR Q1= CALCULATE(SUMX('A_6_Strat Full','A_6_Strat Full'[REV]),DATESINPERIOD('D_7_Calendar'[Date],DATEADD(FIRSTDATE('D_7_Calendar'[Date]),-2,MONTH),3,MONTH),ALLSELECTED('A_6_Strat Full'[Id Customer]))
VAR Q2=CALCULATE(SUMX('A_6_Strat Full','A_6_Strat Full'[REV]),DATESINPERIOD('D_7_Calendar'[Date],DATEADD(FIRSTDATE('D_7_Calendar'[Date]),-5,MONTH),3,MONTH),ALLSELECTED('A_6_Strat Full'[Id Customer]))
VAR Q3=CALCULATE(SUMX('A_6_Strat Full','A_6_Strat Full'[REV]),DATESINPERIOD('D_7_Calendar'[Date],DATEADD(FIRSTDATE('D_7_Calendar'[Date]),-8,MONTH),3,MONTH),ALLSELECTED('A_6_Strat Full'[Id Customer]))
VAR Q4=CALCULATE(SUMX('A_6_Strat Full','A_6_Strat Full'[REV]),DATESINPERIOD('D_7_Calendar'[Date],DATEADD(FIRSTDATE('D_7_Calendar'[Date]),-11,MONTH),3,MONTH),ALLSELECTED('A_6_Strat Full'[Id Customer]))
Var Tot=(Q1+Q2+Q3+Q4)
VAR Ave= Tot/4
VAR Desvest= (((Q1-Ave)^2 +(Q2-Ave)^2+(Q3-Ave)^2+(Q4-Ave)^2)/3)^(1/2)
Var COV = Ave/Desvest
RETURN
IF(ISBLANK(COV),BLANK(),COV)

This measure generates an output like this when in a table with the Id Customer and Segment.

Output COV

From this point on, what I need is to determine a Median based on the records that have the same segment

Median per Segment

  in order to have an output just like this

Desired Output

I’ve tried several approaches but to be frank I’ve been unable to get it right. Do you have any suggestion on how to get around this issue?

I would appreciate any help that you guys can give me 😊

[v-yulgu-msft]

Hi @hzapiainr,

You can try MEDIANX function to return the median of numbers in a measure.

Best regards,

Yuliana Gu

[hzapiainr]

Hi @v-yulgu-msft, 

Thanks for your prompt response,

I've already tried Medianx however there are two issues on using it directly:

1) I can't find any way to determine the medians per category (in this case Segment) as the image with my desired output.

2) If I use MedianX integrated with time intelligence functions, I dont get the desired output; the way I've tried and failed is the next one:

Measure = CALCULATE(Medianx(A3_Merge,[COV]),DATESINPERIOD('C1_Calendar'[Date],DATEADD(FIRSTDATE('C1_Calendar'[Date]),-11,MONTH),12,MONTH),allselected(A3_Merge[Sucursal]))

where A3_Merge[Sucursal] is a slicer in the report with other types of attributes.

Any sugestions on how to workaround this?

Kind Regards

[hzapiainr · Accepted solution]

So I have found a waym around this, pretty much I had to reconfigure my data to allow for the desired result, and calculate my covariance in several steps applyng the same filters along all the way. Here's an example:

Z_1_B_TOT = SUMX(SUMMARIZE('A_09_COV','A_09_COV'[Id Cliente],'A_09_COV'[Segmento_Rel],"Total",SUM('A_09_COV'[Value])),[Total])

Z_2_AveQ = [Z_1_B_TOT]/4

Z_3_Desvest = STDEVX.S(SUMMARIZE('A_09_COV',A_09_COV[Id Cliente],'A_09_COV'[Segmento_Rel],A_09_COV[Attribute],"Desvest",[Z_1_B_TOT]),[Desvest])

Z_4_COV = DIVIDE([Z_2_AveQ],[Z_3_Desvest],0)

1_F_OrderConsistency = 
VAR GralMedian = IF(ISBLANK([Z_4_COV]),BLANK(),CALCULATE(MEDIANX(SUMMARIZE('A_09_COV','A_09_COV'[Id Cliente],'A_09_COV'[Segmento_Rel],"Medx",[Z_4_COV]),[Medx]),ALLSELECTED(C_1_IdClienteDB[Id Cliente])))
RETURN
SWITCH(TRUE(),ISBLANK([Z_4_COV]),BLANK(),[Z_4_COV]>1.5*GralMedian, "D",AND([Z_4_COV]<=1.5*GralMedian,[Z_4_COV]>GralMedian),"C",AND([Z_4_COV]<=GralMedian,[Z_4_COV]>.5*GralMedian),"B",[Z_4_COV]<=.5*GralMedian,"A","D")