cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper II

## calculating 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
1 ACCEPTED SOLUTION
Community Support

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 6
Community Support

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.

Helper II

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.

Helper II

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::

Helper II

Works perfectly - thank you!

Helper II

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.

Helper II

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?

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors