Dynamic average specific cost calculation

[Broot]

Hi

I am trying to create a report for production costs across multiple production units, but I am having issues with calculating average specific costs.

I have cost and production data as two separate tables. These are gatherered from different sources and summed up a bit differently. Production is daily totals and costs are monthly totals (but in the tables I treat it like all costs occur on the first of every month, so the date format between the two tables are the same. The structure of the tables are as follows:

(the Items-column is referring to different types of items used/consumed in production)

Cost Table

Unit	Product	Item	Cost	ts	UnitProduct
A	SW	ingA	1	2020-01-01	A SW
A	HW	ingB	3	2020-01-01	A HW
B	SW	ingD	2	2020-01-02	B SW
A	HW	ingA	5	2020-01-02	A HW
C	HW	ingB	3	2020-01-02	C HW
C	HW	ingC	2	2020-01-02	C HW
...	...	...	...	...	...

Production Table

Unit	Product	Production	ts	UnitProduct
A	SW	3	2020-01-01	A SW
B	SW	3	2020-01-02	B SW
B	HW	2	2020-01-03	B HW
C	SW	1	2020-01-04	C SW
A	HW	4	2020-01-05	A HW
C	HW	3	2020-01-06	C HW
...	...	...	...	...

Now what I am trying to achieve is to be able to select a period of time in the report and have it calculate the average item cost per product for each type of product at each unit, so if I for example selected the period 2020-01-01 to 2020-12-31 I would expect something like the following:

Unit	Product	Item	Cost/Product
A	HW	ingB	0,42
A	SW	ingA	0,31
B	SW	ingD	0,22
C	HW	ingB	0,34
C	HW	ingC	0,37
...	...	...	...

(All values are just made up examples so I do not expect specifically the results shown, it is just to demonstrate the desired format of the results)

I have (sort of) been able to do this by creating an active relationship between the two tables (many to many) between the UnitProduct columns and then create a measure with the following formula:

Specific_Cost = DIVIDE(SUM('Cost_Data'[value]), SUM('Production'[value]))

This works, EXCEPT I have to use TWO date-slicers, one to select the time period for the cost data and another to select the time period for the production data (which of course needs to be the same). I have tried creating a passive connection between the tables date columns, create a separat date table that connects to both tables, switching orders of relationships, adjusting the formula etc but I have not been able to get it working as I want: to be able to select a period from one slicer and then get the average item cost per product at each unit.

Im guessing this is a fairly easy problem to solve (most likely in more than one way) but my mind is just completely blocked at the moment. Hopefully someone can explain what I am missing.

[Anonymous · Accepted solution]

Hi @Broot ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Create a date dimension table(don't create any relationship with other tables)

2. Apply the date field in the above date dimension table on the slicer

3. Create a measurea as below to get the special cost

specific_Cost = 
VAR _mindate =
    MIN ( 'Date'[Date] )
VAR _maxdate =
    MAX ( 'Date'[Date] )
VAR _cost =
    CALCULATE (
        SUM ( 'Cost_Data'[Cost] ),
        FILTER (
            'Cost_Data',
            'Cost_Data'[ts] >= _mindate
                && 'Cost_Data'[ts] <= _maxdate
        )
    )
VAR _product =
    CALCULATE (
        SUM ( 'Production'[Production] ),
        FILTER (
            'Production',
            'Production'[ts] >= _mindate
                && 'Production'[ts] <= _maxdate
        )
    )
RETURN
    DIVIDE ( _cost, _product )

Since I don't know what your actual calculation logic is for the Cost/Product field in the Expected Results table, it's possible that the final value returned is not the result you want. Later on, you can update the formula of above measure to get the results you want. Or if you can share more information about the calculation logic, we can provide you with a solution...

---

Expected Results

Unit	Product	Item	Cost/Product？？
A	HW	ingB	0,42
A	SW	ingA	0,31
B	SW	ingD	0,22
C	HW	ingB	0,34
C	HW	ingC	0,37

---

Best Regards

[Broot]

Hello @Anonymous 

Thank you for the solution, it did exactly what I was trying to do 👍

I was actually able to solve this issue myself by creating a separate Date table and connecting that to both the Cost and Production table with an active relationship (many to one, single direction) and also creating a separate UnitProduct table and connection that to both the Cost and Production tables with an active relationship (many to one, single direction) as well. I remade the report from scratch and the second time around it worked so I probably got something mixed up the first time.

But I do prefer your solution over my own as I don't have to set an active relationship between the Date table and the other tables, which means I have more freedom to set up other connections between the tables for other functions in the report.

Your solution also inspired me to create some other useful measures I can use in the report, without having to think about current relationships.

Thank you!