Calculation DAX

[nesrinehal20]

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

[Anonymous]

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.

[nesrinehal20]

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

[Anonymous · Accepted solution]

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.

[PhilipTreacy]

@nesrinehal20 

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

[nesrinehal20]

 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

[PhilipTreacy]

@nesrinehal20 

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

[nesrinehal20]

i have excel imported in powerbi, realtionship are created. i want to know the dax calculation measure

[PhilipTreacy]

@nesrinehal20 

Please show us your data.  Hard to write DAX when you have to guess what the data is!

[FreemanZ]

hi @nesrinehal20 ,

how are OF and PF related?

[FreemanZ]

hi @nesrinehal20 

try like:

consumption value =

SUMX(Purchase, Purchase[pmp]*RELATED(OF([actual cost]))

for material cost, how are OF and PF related?

[nesrinehal20]

OF and PF are related by num_of