Forum Discussion
Helper IIcalculating total costs with look up
Hello All,
I'm looking into postal costs for our business and have two tables.
The first being a look up table with:
Postal Class (1st or 2nd)
Clean (Yes or No)
Classification (0-100g, 0-100g Large Letter, 101-250g etc)
Cost
The Second Table being a record of items sent with the same fields.
So I would like to lookup the volume of items sent in the record table and then calculate the total cost by multiplying this by the unit cost from the data table. There is a little caveat, in the data table we also have postal classes like airmail and courier which are charged on a per unit basis and the total entered into a "cost" column.
So I need a means of getting to a Total expenditure cost for each department. I've tried using Lookup but it doesn't appear to like or want to use fields from a different table..
| PostalClass | Classification | Clean | Cost |
| First Class | 0 - 100g | N/A | 0.47 |
| First Class | 0 - 100g Large Letter | N/A | 0.71 |
| First Class | 101 - 250g | N/A | 1 |
| First Class | 251 - 500g | N/A | 1.33 |
| First Class | 501 - 750g | N/A | 1.87 |
| Second Class | 0 - 100g | Yes | 0.288 |
| Second Class | 0 - 100g Large Letter | Yes | 0.46 |
| Second Class | 101 - 250g | Yes | 0.8 |
| Second Class | 251 - 500g | Yes | 1.07 |
| Second Class | 501 - 750g | Yes | 1.51 |
| Second Class | 0 - 100g | No | 0.33 |
| Second Class | 0 - 100g Large Letter | No | 0.56 |
| Second Class | 101 - 250g | No | 0.8 |
| Second Class | 251 - 500g | No | 1.07 |
| Second Class | 501 - 750g | No | 1.51 |
| Packets | 0 - 100g | N/A | 3.225 |
| Packets | 101 - 250g | N/A | 3.225 |
| Packets | 251 - 500g | N/A | 3.225 |
| Packets | 501 - 750g | N/A | 3.225 |
| Packets | 751 - 1000g | N/A | 3.225 |
| Packets | 1001 - 1250g | N/A | 5.695 |
| ServiceArea | OrderQuantity | EarliestSendDate | LatestSendDate | PostalClass | Clean | ItemCost | Urgency | UrgencyUpdateSent | Classification | NumberofPages | LeafletsRequired | Source | Modified | CreatedDate | CreatedTime | Title |
| Dept A | 1 | 04-May-23 | 04-May-23 | Second Class | No | Overdue | 02/05/2023 16:00 | 0 - 100g | 1 | No | Portal | 04/05/2023 08:29 | 02-May-23 | ########### | POST-2023-000043 | |
| Dept A | 1 | 02-May-23 | 02-May-23 | First Class | Urgent | 03/05/2023 11:00 | 3 | No | Portal | 04/05/2023 08:42 | 03-May-23 | ########### | POST-2023-000044 | |||
| Dept A | 1 | 02-May-23 | 03-May-23 | Second Class | Urgent | 03/05/2023 13:00 | 3 | No | Portal | 04/05/2023 08:42 | 03-May-23 | ########### | POST-2023-000045 | |||
| Dept A | 1 | 02-May-23 | 03-May-23 | Second Class | Urgent | 2 | No | Portal | 04/05/2023 08:38 | 03-May-23 | ########### | POST-2023-000046 | ||||
| Dept A | 2 | 02-May-23 | 04-May-23 | Second Class | Urgent | 2 | No | Portal | 04/05/2023 08:41 | 03-May-23 | ########### | POST-2023-000047 | ||||
| Dept A | 5 | 05-May-23 | 05-May-23 | Second Class | Yes | Urgent | 0 - 100g | 10 | No | Portal | 05/05/2023 09:20 | 04-May-23 | ########### | POST-2023-000051 | ||
| Dept A | 5 | 05-May-23 | 05-May-23 | Second Class | Yes | Urgent | 0 - 100g | 10 | No | Portal | 05/05/2023 09:37 | 04-May-23 | ########### | POST-2023-000052 | ||
| Dept A | 3 | 05-May-23 | 05-May-23 | Second Class | Yes | Urgent | 0 - 100g | 6 | No | Portal | 05/05/2023 09:36 | 04-May-23 | ########### | POST-2023-000053 | ||
| Dept B | 25 | 09-May-23 | 09-May-23 | Second Class | Yes | Normal | 0 - 100g | 1 | No | Portal | 09/05/2023 08:04 | 05-May-23 | ########### | POST-2023-000055 | ||
| Dept B | 11 | 09-May-23 | 09-May-23 | Second Class | Yes | Normal | 0 - 100g | 1 | No | Portal | 09/05/2023 08:43 | 05-May-23 | ########### | POST-2023-000056 | ||
| Dept C | 1 | 09-May-23 | 09-May-23 | Second Class | Yes | Normal | 0 - 100g | 2 | No | Portal | 09/05/2023 08:50 | 05-May-23 | ########### | POST-2023-000057 | ||
| Dept A | 5 | 09-May-23 | 09-May-23 | Second Class | Yes | Urgent | 0 - 100g | 5 | No | Portal | 09/05/2023 09:10 | 05-May-23 | ########### | POST-2023-000058 | ||
| Dept A | 4 | 09-May-23 | 09-May-23 | Second Class | Yes | Urgent | 0 - 100g | 4 | No | Portal | 09/05/2023 09:15 | 05-May-23 | ########### | POST-2023-000059 | ||
| Dept C | 5 | 10-May-23 | 10-May-23 | Second Class | Yes | Urgent | 0 - 100g | 10 | No | Portal | 10/05/2023 08:14 | 09-May-23 | ########### | POST-2023-000060 | ||
| Dept C | 1 | 10-May-23 | 10-May-23 | Second Class | Yes | Urgent | 0 - 100g | 4 | No | Portal | 10/05/2023 08:24 | 09-May-23 | ########### | POST-2023-000061 | ||
| Dept D | 2 | 10-May-23 | 10-May-23 | Airmail | Yes | £12.58 | Urgent | 2 | No | Portal | 10/05/2023 08:34 | 09-May-23 | ########### | POST-2023-000062 | ||
| Dept D | 4 | 10-May-23 | 10-May-23 | Second Class | Yes | Urgent | 0 - 100g | 8 | No | Portal | 10/05/2023 08:52 | 10-May-23 | ########### | POST-2023-000063 | ||
| Dept D | 4 | 10-May-23 | 10-May-23 | Second Class | Yes | Urgent | 0 - 100g | 8 | No | Portal | 10/05/2023 08:58 | 10-May-23 | ########### | POST-2023-000064 | ||
| Dept A | 5 | 11-May-23 | 11-May-23 | Second Class | Yes | Urgent | 0 - 100g | 5 | No | Portal | 11/05/2023 07:39 | 10-May-23 | ########### | POST-2023-000066 | ||
| Dept B | 1 | 11-May-23 | 11-May-23 | Second Class | Yes | Urgent | 0 - 100g | 8 | No | Portal | 11/05/2023 08:38 | 11-May-23 | ########### | POST-2023-000068 | ||
| Dept A | 5 | 11-May-23 | 11-May-23 | Second Class | Yes | Urgent | 0 - 100g | 5 | No | Portal | 11/05/2023 10:41 | 11-May-23 | ########### | POST-2023-000069 | ||
| Dept A | 1 | 12-May-23 | 12-May-23 | Second Class | Yes | Urgent | 0 - 100g | 1 | No | Portal | 12/05/2023 07:52 | 11-May-23 | ########### | POST-2023-000071 | ||
| Dept D | 5 | 12-May-23 | 12-May-23 | Second Class | Yes | Urgent | 0 - 100g | 10 | No | Portal | 12/05/2023 08:00 | 11-May-23 | ########### | POST-2023-000072 | ||
| Dept D | 1 | 12-May-23 | 12-May-23 | Second Class | Yes | Urgent | 0 - 100g | 2 | No | Portal | 12/05/2023 08:06 | 11-May-23 | ########### | POST-2023-000073 | ||
| Dept B | 1 | 12-May-23 | 12-May-23 | Second Class | Yes | Urgent | 0 - 100g | 1 | No | Portal | 12/05/2023 08:17 | 12-May-23 | ########### | POST-2023-000074 |
Hi SACooper ,
You could try the below measure:
Total Cost = CALCULATE( SUM(DataTable[Cost]), FILTER( DataTable, DataTable[PostalClass] = SELECTEDVALUE(RecordTable[PostalClass]) && DataTable[Classification] = SELECTEDVALUE(RecordTable[Classification]) && DataTable[Clean] = IF(ISBLANK(SELECTEDVALUE(RecordTable[Clean])), "N/A", SELECTEDVALUE(RecordTable[Clean])) ) ) * SUM(RecordTable[OrderQuantity])You can replace DataTable and RecordTable with the actual names of your tables.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Hi SACooper ,
You could try the below measure:
Total Cost = CALCULATE( SUM(DataTable[Cost]), FILTER( DataTable, DataTable[PostalClass] = SELECTEDVALUE(RecordTable[PostalClass]) && DataTable[Classification] = SELECTEDVALUE(RecordTable[Classification]) && DataTable[Clean] = IF(ISBLANK(SELECTEDVALUE(RecordTable[Clean])), "N/A", SELECTEDVALUE(RecordTable[Clean])) ) ) * SUM(RecordTable[OrderQuantity])You can replace DataTable and RecordTable with the actual names of your tables.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
SACooperThank you for this - sorry to have not replied sooner, got pulled into another project last minute. I'm going to take a look at this now.
- SACooper
Hi - that project took far longer than expected.
I'm back on this little one and the formula sort of works ok just a little oddity.
In this screenshot the costs for HRC aren't summing correctly.
I'd be expecting a figure of 17.63 instead of the 11.81
The 11.81 is the product of all 41 items at the 2nd class cost
hoping its a quick fix?
- SACooper
Works perfectly - thank you!
- SACooper
ok maybe a small snaffu somehwere...
I've dumped the two tables from PowerBI into excel and made sure that all the fields match i.e. First Class, N/A, and 0 - 100g -- I would have been expecting a figure of £22.09 rather than the blank.
::never mind think I had done something silly::