Forum Discussion

dokat's avatar
dokat
Post Prodigy
4 years ago
Solved

Sum divided by unique count

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

 

StateCensus DivisionDollar Sales
AlabamaEast South Central100
ArkansasWest South Central200
ArizonaMountain300
CaliforniaPacific400
ColoradoMountain500
ConnecticutNew England600
District of ColumbiaSouth Atlantic900
DelawareSouth Atlantic800
FloridaSouth Atlantic900
GeorgiaSouth Atlantic1000
MarylandSouth Atlantic1100
North CarolinaSouth Atlantic1200
South CarolinaSouth Atlantic1300
VirginiaSouth Atlantic300
West VirginiaSouth Atlantic1400
KentuckyEast South Central1500
GeorgiaSouth Atlantic100
MassachusettsNew England1800
MaineNew England1800
MaineNew England2000
Need Help
  • vojtechsima's avatar
    vojtechsima
    4 years ago

    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

     

2 Replies

  • vojtechsima's avatar
    vojtechsima
    Super User
    4 years ago

    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