Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello
will you please help?
I have to calculate cost per unit of production for each department, i.e. how much each department brings to a unit cost.
Please see below the database structure. Both volume and cost data sit in _data_table which is basically an accounting ledger.
I can calculate through a measure a trend of base cost per month per department (with the help of non-related calendar filter).
Volume sits in Production accounts and not in the department hierarchy.
I need to show a table with a list of departments and months with a value equal to each department cost divided by the volume of production in that month.
Will you please help solving this?
Many thanks
Mira
Solved! Go to Solution.
Hello All
If interested, I found a solution. I created a separate calculated table for volumes by month, so that there is no connection to Cost Code lookup. Then a measure to divide the departmental costs by the volume by month. My problem was that if I calculated it all from the same datatable the Cost Code active relationship was holding it from showing results by departments.
If interested please let me know.
Thank you
Mira
@Anonymous - Difficult to understand exactly how the calculation needs to be. Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
I have the following datatable:
Ledger | Account Code | Cost Code | AccPeriod | Transaction Amount |
A | 705320 | 112OPX132 | 01-Sep-18 | 1,190.16 |
A | 705320 | 131OPX090 | 01-Sep-18 | 1,644.55 |
A | 705320 | 132OPX042 | 01-Sep-18 | 3,344.00 |
A | SALEGAS | 132REV001 | 01-Sep-18 | - 2,328.88 |
A | SALEGAS | 111REV001 | 01-Sep-18 | 10.38 |
A | SALEGAS | 112REV001 | 01-Sep-18 | 33.36 |
A | SALENGL | 132REV002 | 01-Sep-18 | 4.79 |
A | SALENGL | 131REV002 | 01-Sep-18 | 693.15 |
I have three measures to calculate (1) total operating costs and (2) total volume and (3) unit costs (1 divided by 2)
Please see below:
I can calculate each department cost as shown above based on Cost Code in the right corner. However, I need a solution to calculate each department's share in the unit costs, as shown
Kind regards
Mira
Hello All
If interested, I found a solution. I created a separate calculated table for volumes by month, so that there is no connection to Cost Code lookup. Then a measure to divide the departmental costs by the volume by month. My problem was that if I calculated it all from the same datatable the Cost Code active relationship was holding it from showing results by departments.
If interested please let me know.
Thank you
Mira