Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

Allocate Shared Cost to Accounts

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)pbi_ss.jpg

 

#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.

1 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] )




Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

13 REPLIES 13
parry2k
Super User
Super User

@Anonymous how tables relationship looks like? 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

@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.

pbi_ss2.jpg

 

@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
)

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

@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
Not applicable

@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.

pbi_ss3.jpg

 

@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?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

@parry2k 

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

@Anonymous do you want this?

 

image.png



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

@parry2k 

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

@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] )




Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

@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
Not applicable

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

@Anonymous i think this is much better

 

image.png



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.