Forum Discussion
Dynamic average specific cost calculation
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
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
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!