cancel
Showing results for 
Search instead for 
Did you mean: 
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

Accepted Solutions
Super User
Super User

Re: Getting total amount then divide by account breakdown

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut! Connect with me on Linkedin






7 REPLIES 7
Community Support Team
Community Support Team

Re: Getting total amount then divide by account breakdown

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 other members find it more quickly.
Anonymous
Not applicable

Re: Getting total amount then divide by account breakdown

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 

Super User
Super User

Re: Getting total amount then divide by account breakdown

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




Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut! Connect with me on Linkedin






Anonymous
Not applicable

Re: Getting total amount then divide by account breakdown

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.

Super User
Super User

Re: Getting total amount then divide by account breakdown

@Anonymous is this you want the expected result to be

 

yield.PNG




Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut! Connect with me on Linkedin






Anonymous
Not applicable

Re: Getting total amount then divide by account breakdown

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

Super User
Super User

Re: Getting total amount then divide by account breakdown

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut! Connect with me on Linkedin