Calculations

[kevbrown1980]

Hi

I have 2 table with joined, 1 table has my cost data and the other the volume data. The cost table is 1 line per team however the volume table is multiple row per department giving an overal volume amount. The table below might show it better. I need to multiple the team cost in table 1 by the Vol in table 2 however I only need 10 for example for Code 1, so basically the unique number in Vol per code. How do I do that?

Table A					Table 2					Result
Code	Team	Cost			Code	Dept	Vol			Team	Total Cost
1	Green	£  200.00			1	A	10			Green	£  2,000.00
2	Yellow	£  430.00			1	B	10			Yellow	£  2,150.00
3	Red	£  127.00			1	C	10			Red	£  1,524.00
4	Orange	£  267.00			1	D	10			Orange	£  8,010.00
5	Blue	£  205.00			2	A	5			Blue	£  4,305.00
					2	B	5
					2	R	5
					3	C	12
					4	H	30
					4	D	30
					4	R	30
					4	T	30
					5	H	21
					5	J	21
					5	F	21
					5	T	21
					5	G	21
					5	D	21
					5	S	21

[Ashish_Mathur]

Hi,

You may write these calculated column formulas in Table1

Volume = calculate(min('Table2'[Vol]),filter('Table2','Table2'[Code]=earlier('Table1'[Code])))

Total = 'Table1'[Cost]*'Table1'[Volume]

Hope this helps.

[v-dineshya]

Hi @kevbrown1980 ,

Thank you for reaching out to the Microsoft Community Forum.

Hi @johnt75 , @danextian , Thank you for your prompt responses.

Hi @kevbrown1980 ,  Could you please try the proposed solutions shared by @johnt75  and @danextian . 

Please do let us know if you have any further queries.

Regards,

Dinesh

[v-dineshya]

Hi @kevbrown1980 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[v-dineshya]

Hi @kevbrown1980 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[v-dineshya]

Hi @kevbrown1980 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[danextian · Accepted solution]

Hi @kevbrown1980 

If the code in Table A is always unique, you can create a one-to-many single direction relationship from Table A to B on code. Then create this measure (assuming there's only one distinct vol value per team regardless of the department):

total cost = 
SUMX (
    VALUES ( TableA[Code] ),
    CALCULATE ( MAX ( TableB[Vol] ) ) * CALCULATE ( SUM ( TableA[Cost] ) )
)

[johnt75]

If you have a one-to-many relationship from 'Table A' to 'Table 2' you can create a measure like

Total Cost = SUMX(
    'Table A',
    VAR Volume = SUMX(
        CALCULATETABLE(DISTINCT('Table 2'[Vol])),
        'Table 2'[Vol]
    )
    VAR Result = 'Table A'[Cost] * Volume
    RETURN
        Result
)