Forum Discussion
Add/sum values based on other column within ID
Hello all,
I am facing quite an issue today. I need to add costs to eachother based on ID. However there is a difficulty with this. Sometimes an ID includes Tax or Fuel, wich is a & based on the type described in the culumn ' Calculate from' , for example ID 1 has a 12% fuel based on both the transport costs in ID 1.
As you also can see, the Tax costs needs to be calculated based on only the ADM in ID 4.
If there are only null values then every amount/currency can just be added within the ID.
| ID | Type | amount | currency | Calculate from |
| 1 | Transport | 400 | EUR | null |
| 1 | Transport | 150 | EUR | null |
| 1 | Fuel | 12 | % | Transport |
| 2 | Transport | 200 | EUR | null |
| 3 | Transport | 300 | EUR | null |
| 3 | Fuel | 20 | % | Transport |
| 4 | Transport | 200 | EUR | null |
| 4 | Adm | 100 | EUR | null |
| 4 | TAKS | 20 | % | ADM |
| 5 | Transport | 650 | EUR | null |
| 5 | Transport | 200 | EUR | null |
Is someone able to help me with this obstacle? If you need more info to assist, please let me know!
Would help me big time if someone has the solution.
1 Reply
I would go for creating a measure :
Basic Costs = SUMX(FILTER(YourTable, ISBLANK(YourTable[Calculate from])), YourTable[amount])Then :
Fuel Charge = SUMX( FILTER( YourTable, YourTable[Type] = "Fuel" ), (YourTable[amount] / 100) * CALCULATE([Basic Costs], FILTER(YourTable, YourTable[ID] = EARLIER(YourTable[ID]) && YourTable[Type] = "Transport")) )
