Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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 |
Solved! Go to Solution.
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.
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.
Hi @v-stephen-msft sorry its been a while - got distracted by other projects that shouldn't have taken as long as they did (scope creep).
I'm taking another look at impementing this but somethings not quite right in that when I try to add the dax to a matrix and then have a cost code/department hieracrchy the costs don't sump up correct.
I've posted a screen shot a bit earlier which shows the oddity.
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::
Works perfectly - thank you!
Thank 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.
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?
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
21 | |
21 | |
21 | |
13 | |
12 |
User | Count |
---|---|
43 | |
28 | |
25 | |
23 | |
22 |