Coefficient of Dispersion

[jarmstrong]

I'm trying to calculate the coefficient of dispersion in Power BI. Ideally this could be calculated for the subset of data select by slicers.

It's calculated by the following equations in excel. I can't seem to get the row level sale ratio subtracted from the set level median.

Any help would be greatly appreciated.

Value	Sale Price
260000	425000	=ROUND(A2/B2,2)	=ABS(C2-$C$14)
65000	100000	=ROUND(A3/B3,2)	=ABS(C3-$C$14)
74389	90000	=ROUND(A4/B4,2)	=ABS(C4-$C$14)
128663	160000	=ROUND(A5/B5,2)	=ABS(C5-$C$14)
900000	1345000	=ROUND(A6/B6,2)	=ABS(C6-$C$14)
1550000	1009900	=ROUND(A7/B7,2)	=ABS(C7-$C$14)
420000	1517800	=ROUND(A8/B8,2)	=ABS(C8-$C$14)
205000	370000	=ROUND(A9/B9,2)	=ABS(C9-$C$14)
650000	1000000	=ROUND(A10/B10,2)	=ABS(C10-$C$14)
104300	285000	=ROUND(A11/B11,2)	=ABS(C11-$C$14)
125500	160000	=ROUND(A12/B12,2)	=ABS(C12-$C$14)
96000	180000	=ROUND(A13/B13,2)	=ABS(C13-$C$14)	COD
		=MEDIAN(C2:C13)	=AVERAGE(D2:D13)	=D14/C14*100

Thank You.

[Anonymous · Accepted solution]

Hi @jarmstrong,

Please refer to below formula to achieve your requirement.

Calculate columns:

Round Value = ROUND([Value]/[Sale Price],2) 

ABS Value = ABS([Round Value]-MEDIANX(ALL(Sales),[Round Value]))

Measures:

Median Round Value = CALCULATE(MEDIAN(Sales[Round Value]),ALL(Sales)) 

AVG of ABS Value = CALCULATE(AVERAGE(Sales[ABS Value]),ALL(Sales)) 

Percent of AVG = [AVG of ABS Value]/[Median Round Value]*100 

Result:

Regards,
Xiaoxin Sheng

[mattbrice]

This was a bit on an interesting problem so i took a try at it.  Here is a link to an Excel 2016 wb you can download.  It should translate easy over to power bi.  

Download Link

[jarmstrong]

Thank you for your effort with this. I've been unable to translate your answer to Power BI.

[mattbrice]

Post a link to what you do have if you can.