Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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]
Regards,
Frank
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.
@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.
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
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |