Sum divided by unique count

[dokat]

Hi,

I have data table in below format where i'd lke to calculate sum divided by unique count.  Basically sum of  dollar sales for Census Division then divide # of unique count of States.

For Ex: In below table South Atlantic sales total is $9,000, i would like this to be divided by 9 since there are 9  unique states  that belongs to the census division and result to be =9000/9 = 1000

State	Census Division	Dollar Sales
Alabama	East South Central	100
Arkansas	West South Central	200
Arizona	Mountain	300
California	Pacific	400
Colorado	Mountain	500
Connecticut	New England	600
District of Columbia	South Atlantic	900
Delaware	South Atlantic	800
Florida	South Atlantic	900
Georgia	South Atlantic	1000
Maryland	South Atlantic	1100
North Carolina	South Atlantic	1200
South Carolina	South Atlantic	1300
Virginia	South Atlantic	300
West Virginia	South Atlantic	1400
Kentucky	East South Central	1500
Georgia	South Atlantic	100
Massachusetts	New England	1800
Maine	New England	1800
Maine	New England	2000

[vojtechsima · Accepted solution]

Hi, @dokat ,
I believe Measure like this will do it:

DividedTotal = 
var _currentCensus = MAX(Financial[Census Division])
var _sum = SUM(Financial[Dollar Sales])
var _uniquestates = CALCULATE(DISTINCTCOUNT(Financial[State]),
    Financial[Census Division]=_currentCensus)

var _calculation = DIVIDE(_sum,_uniquestates)
return _calculation

[dokat]

@vojtechsima  Thank you it worked perfectly!