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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Getting total amount then divide by account breakdown

Hi,

 

I am trying to do a simple DIVIDE( "Cost by Account", "Total volume") and am stuck where the Total Volume isn't broken down into accounts.

 

I have 2 DAX measures:

 

Total Volume =
CALCULATE(SUM('OPERATING STATEMENT'[Value]), FILTER('OPERATING STATEMENT', 'OPERATING STATEMENT'[Account_OS] = "ABC Tonnes" || 'OPERATING STATEMENT'[Account_OS] = "DEF tonnes"))

 

Cost by Account =
CALCULATE(SUM('OPERATING STATEMENT'[Value]), FILTER('OPERATING STATEMENT', 'OPERATING STATEMENT'[Account_OS] = "Administration Expenses" || 'OPERATING STATEMENT'[Account_OS] = "Production Expenses"))

 

What I would like to do is calculate the Yield for each MCC_Account below into a measure called "Yield"

 

My table currently looks like this:

 

MCC_Account               Total Volume           Cost By Account           Yield

[BLANK]                                     100                                                        

Labour                                                                        25,000               250

Hire                                                                            27,000               270

Fuel                                                                            15,000               150

Total                                           100                          67,000               670

 

Any ideas?

 

Thanks

1 ACCEPTED SOLUTION

@Anonymous here is the change in your measure

 

Total Volume = 
CALCULATE(SUM('OPERATING STATEMENT'[Value]), 
FILTER(ALLSELECTED( 'OPERATING STATEMENT' ),
'OPERATING STATEMENT'[Account_OS] = "ABC Tonnes" || 'OPERATING STATEMENT'[Account_OS] = "DEF Tonnes"))


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

7 REPLIES 7
v-frfei-msft
Community Support
Community Support

Hi @Anonymous,

 

I made one sample for your reference. If it doesn't meet your requirement, kindly share your sample data to me.

 

Cost by Account = 
CALCULATE(SUM('OPERATING STATEMENT'[Value]), FILTER('OPERATING STATEMENT', 'OPERATING STATEMENT'[Account_OS] = "Administration Expenses" || 'OPERATING STATEMENT'[Account_OS] = "Production Expenses"))
Total Volume = 
CALCULATE(SUM('OPERATING STATEMENT'[Value]), FILTER('OPERATING STATEMENT', 'OPERATING STATEMENT'[Account_OS] = "ABC Tonnes" || 'OPERATING STATEMENT'[Account_OS] = "DEF tonnes"))
Yield = [Cost by Account]/[Total Volume]

Capture.PNG

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Anonymous
Not applicable

Thanks Frank, yeah I tried that and as a total it works fine, however, I need each MCC Account to show the % of total Cost by Account.

 

Sample data below. % broken down below to the desired result I need in DAX e.g. consumables - total 2500 and yield 31.3% (2500 / 8000). The numbers are made up so don't really make sense as a calculation but it's rather about getting the calculation correct. 

 

Thanks.

 

Capture.PNG 

@Anonymous it is very important to understand how your data table look like, in your original post sample data you showed is in totally different format than most recent reply.

 

How does your data looks like? Can you share?



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

Sure, the last sample was just a csv extract. A test.pbix file is shared here: https://1drv.ms/f/s!ArxsIP-W81kFgp5jpC0jwe001U15AQ

 

My actual table will by 500k plus rows however the columns in this test files are same i.e. one column for Account_OS and one column for MCC Account. All "Tonnes" rows in account_OS will have a blank MCC Account.

@Anonymous is this you want the expected result to be

 

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

Yes, that's exactly what I need. Could you share your measure please? Thanks.

@Anonymous here is the change in your measure

 

Total Volume = 
CALCULATE(SUM('OPERATING STATEMENT'[Value]), 
FILTER(ALLSELECTED( 'OPERATING STATEMENT' ),
'OPERATING STATEMENT'[Account_OS] = "ABC Tonnes" || 'OPERATING STATEMENT'[Account_OS] = "DEF Tonnes"))


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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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