Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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.
Solved! Go to 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.
@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.
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.
@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.
"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.
@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.
@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 do you want this?
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 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.
@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 i think this is much better
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
59 | |
57 |