Forum Discussion
Calculations
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 |
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] ) ) )
7 Replies
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 )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] ) ) )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
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
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
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.
