Forum Discussion
Calculation DAX
I have a Purchase file (article_code, designation, family, pmp) and file OF (num_of, article_code, designation, family, actual cost) and file PF (article_code, quantity,num_of) I want to calculate consumption value = actual cost * pmp and then calculate material cost = consumption/quantity value. how to do this calculation in dax powerbi
Hi nesrinehal20
Please try the following possible solutions:
1. First, create the following measures to calculate total actual cost and pmp.
TotalActualCost = CALCULATE( SUM('OF'[actual cost]), ALLEXCEPT('OF', 'OF'[num_of]) )TotalPMP = CALCULATE( SUMX( 'OF', RELATED('PURCHASE'[pmp]) ), ALLEXCEPT('OF', 'OF'[num_of]) )2. Create the following measures to calculate consumption and material cost.
consumption = [TotalActualCost] * [TotalPMP]material cost = DIVIDE( [consumption], CALCULATE( SUM('PF'[quantity]), ALLEXCEPT('PF', 'PF'[num_of]) ) )Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
11 Replies
Hi nesrinehal20
The DAX used in the following tests may not fully meet your requirements as the example data you provided is too small for us to accurately perform aggregation calculations on the data. If this does not solve your problem, please provide slightly more example data and explain in detail what you are trying to achieve.
This is the relationship I've established, so please correct me if I've misinterpreted it.Create the measures with the following DAX：
consumption = SUMX( 'PURCHASE', 'PURCHASE'[pmp] * RELATED('OF'[actual cost]) )material cost = DIVIDE([consumption], SUM(PF[quantity]), 0)Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.ok done but if the same item has several real cost and pmp values and I must add them before multiplying them how to do it
Hi nesrinehal20
Please try the following possible solutions:
1. First, create the following measures to calculate total actual cost and pmp.
TotalActualCost = CALCULATE( SUM('OF'[actual cost]), ALLEXCEPT('OF', 'OF'[num_of]) )TotalPMP = CALCULATE( SUMX( 'OF', RELATED('PURCHASE'[pmp]) ), ALLEXCEPT('OF', 'OF'[num_of]) )2. Create the following measures to calculate consumption and material cost.
consumption = [TotalActualCost] * [TotalPMP]material cost = DIVIDE( [consumption], CALCULATE( SUM('PF'[quantity]), ALLEXCEPT('PF', 'PF'[num_of]) ) )Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hi nesrinehal20
try like:
consumption value =
SUMX(Purchase, Purchase[pmp]*RELATED(OF([actual cost]))
for material cost, how are OF and PF related?
OF and PF are related by num_of
Are these CSV files, Excel, PBIX?
You shoudl have all the data (files) in a single PBIX fiel where you can create relationships between the data tables. DAX Measures can then be written to do what you want.
Without the files/data it's hard to give you much more than this so can you please supply samples of these files?
Regards
Phil
i have excel imported in powerbi, realtionship are created. i want to know the dax calculation measure
You said that OF and PF are related by num_of but the images you shared of the data don't show that column.
Also we can't really work with images, providing data as tables or ideally providing the PBIX file you are using makes our jobs as volunteers much easier. Typing out all the data in your (incomplete) images takes some time and will be prone to errors, making the answers we give you no use.
Please check this which might explain things better than I have
Re: How to Get Your Question Answered Quickly - Page 7 - Microsoft Fabric Community
Regards
Phil
PURCHASE FILE
code_article designation family month pmp MPO0239 acide citrique MP 1 3,71 EMO2636 Présentoir EM 2 3.5 OF FILE
num_of code_article designation famille actual cost OF2401-men001 EM02636 presentoir EM 1150 OF2501-pro002 MPO0239 acide MP 120.5
PF FILE
site num_of code_pf designation_pf quantity men OF2401-men001 PFSN036 Juto 1500 PRO of2501-pro002 PRO012 choco 200
