Allocate Shared Cost to Accounts

[Anonymous]

Context:  Attempting to assign the total of shared-services costs to all consuming accounts.  When i get to the end calculation (#4), I don't ever see the totals increasing when I'd expect to see them.

Data Relationship:
YellowTable has 1:Many relationship to GreenTable of an account number

Data Sample (Excel for representation only)

#1 (Measure) count the # of accounts where "sharedServicesUsed=1"
X_accts = CALCULATE(DISTINCTCOUNT('yellowtable'[accountname]),'yellowtable'[sharedservicesused]=1)

#2 (Measure) take the SubTotal applied to accounts "overHeadAcct=1"
X_overhead = CALCULATE('greentable'[subtotal],FILTER('yellowtable','yellowtable'[overheadacct]=1))

#3 Divide: #2 by #1 to get the allocation per account value
X_divide = DIVIDE([X_overhead],[X_accts])

#4 apply #3 value to each account where "sharedServicesUsed=1"
X_sum = CALCULATE([subtotal]+[X_divide])

In the sample data (Excel), there are (4) accounts where Shared Services are used. $50/4 = $12.50 allocation to each one "Shared Allocation".  In reality I don't ever see this get allocated in PowerBI.

Any guidance on step #4 (or other) to allocate the shared cost appropriately would be appreciated.  Thank you.

[parry2k]

@Anonymous how tables relationship looks like? 

[Anonymous]

@parry2k 

Today there is a relationship of account number in yellow (1) to acct ID in Green (many).  It helps me pull over cost-center values and other elements.

[parry2k]

@Anonymous ok

so i think in following you don't need to do distinctcount, i'm sure account name is already unique.

X_accts = CALCULATE(DISTINCTCOUNT('yellowtable'[accountname]),'yellowtable'[sharedservicesused]=1)

change this measure

X_overhead =
CALCULATE
(
   SUM('greentable'[subtotal]),
   'yellowtable'[overheadacct]=1
)

[Anonymous]

@parry2k 

"Subtotal" itself is a measure, not a column in the raw table itself.  When I try and use SUM it can't even find the reference column in the table.

I'll see if I can scrub up the data enough to export.

[Anonymous]

@parry2k  thank you

Yes, in yellow table there are no duplicate accounts. I flipped it to COUNTROWS to get an aggregate count of accounts

X_accts = CALCULATE(COUNTROWS('yellowtable'),'yellowtable'[sharedservicesused]=1)

My Overhead costs look right when i filter all overhead accounts only. I wasn't able to use SUM, i had to use SUMX to get it though

X_OVERHEAD = CALCULATE(SUMX('greentable','greentable'[subtotal]),'yellowtable'[overheadacct]=1)

No change yet to #4 though where we apply the $8.50 "X_divide" to each acct subtotal.

[parry2k]

@Anonymous not sure why you cannot use sum but have to use sumx, seems like I'm missing something here.

Can you share sample data/pbix file for me to look into it?

[Anonymous]

@parry2k 

Was able to scrub/export pbix - download here.  Thanks again.

[parry2k]

@Anonymous do you want this?

[Anonymous]

@parry2k 

Yes, that looks allocated correctly based on the flag conditions!

[parry2k · Accepted solution]

@Anonymous here are updated measures

X_accts = CALCULATE(COUNTROWS('yellowtable'),ALL(yellowtable ), 'yellowtable'[sharedservicesused]=1)

X_OVERHEAD = CALCULATE([Sub Total], ALL( greentable ), yellowtable[overheadacct] = 1 ) 

X_divide = 
CALCULATE( 
    SUMX( 
        VALUES( yellowtable[accountnumber] ), 
        DIVIDE([X_overhead],[X_accts])  
    ), 
    yellowtable[sharedservicesused] = 1 
) * 
DIVIDE( [Sub Total], [Sub Total] )

[Anonymous]

@parry2k Thanks again!  Looks good on my end as well with the sample data.  In my real-data report, i have a few small discrepancies but nothing that I can't track down.  I appreciate you sharing your knowledge on solving this type of problem.

[Anonymous]

@parry2k Thank you - testing the outputs now, I will report back.

[parry2k]

@Anonymous i think this is much better