Cost per unit for each department

[Anonymous]

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

[Greg_Deckler]

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

[Anonymous]

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

[Anonymous · Accepted 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]

@Greg_Deckler  

Is my problem clear? 🙂 I am not sure...

Thank you

Mira