Solved: Re: calculating total costs with look up

SACooper · ‎05-12-2023

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Second Class

Yes

Urgent

0 - 100g

1

No

Portal

12/05/2023 08:17

12-May-23

###########

POST-2023-000074

v-stephen-msft · ‎05-16-2023

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.

View solution in original post

v-stephen-msft · ‎05-16-2023

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.

SACooper · ‎06-26-2023

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.

SACooper · ‎05-17-2023

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

SACooper · ‎05-17-2023

Works perfectly - thank you!

SACooper · ‎05-17-2023

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.

SACooper · ‎06-23-2023

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?

calculating total costs with look up

Helpful resources

Power BI Monthly Update - September 2024

Microsoft Fabric & AI Learning Hackathon

Fabric Community Update - September 2024