Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
SACooper
Helper II
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..

PostalClassClassificationCleanCost
First Class0 - 100gN/A0.47
First Class0 - 100g Large LetterN/A0.71
First Class101 - 250gN/A1
First Class251 - 500gN/A1.33
First Class501 - 750gN/A1.87
Second Class0 - 100gYes0.288
Second Class0 - 100g Large LetterYes0.46
Second Class101 - 250gYes0.8
Second Class251 - 500gYes1.07
Second Class501 - 750gYes1.51
Second Class0 - 100gNo0.33
Second Class0 - 100g Large LetterNo0.56
Second Class101 - 250gNo0.8
Second Class251 - 500gNo1.07
Second Class501 - 750gNo1.51
Packets0 - 100gN/A3.225
Packets101 - 250gN/A3.225
Packets251 - 500gN/A3.225
Packets501 - 750gN/A3.225
Packets751 - 1000gN/A3.225
Packets1001 - 1250gN/A5.695

 

ServiceAreaOrderQuantityEarliestSendDateLatestSendDatePostalClassCleanItemCostUrgencyUrgencyUpdateSentClassificationNumberofPagesLeafletsRequiredSourceModifiedCreatedDateCreatedTimeTitle
Dept A104-May-2304-May-23Second ClassNo Overdue02/05/2023 16:000 - 100g1NoPortal04/05/2023 08:2902-May-23###########POST-2023-000043
Dept A102-May-2302-May-23First Class  Urgent03/05/2023 11:00 3NoPortal04/05/2023 08:4203-May-23###########POST-2023-000044
Dept A102-May-2303-May-23Second Class  Urgent03/05/2023 13:00 3NoPortal04/05/2023 08:4203-May-23###########POST-2023-000045
Dept A102-May-2303-May-23Second Class  Urgent  2NoPortal04/05/2023 08:3803-May-23###########POST-2023-000046
Dept A202-May-2304-May-23Second Class  Urgent  2NoPortal04/05/2023 08:4103-May-23###########POST-2023-000047
Dept A505-May-2305-May-23Second ClassYes Urgent 0 - 100g10NoPortal05/05/2023 09:2004-May-23###########POST-2023-000051
Dept A505-May-2305-May-23Second ClassYes Urgent 0 - 100g10NoPortal05/05/2023 09:3704-May-23###########POST-2023-000052
Dept A305-May-2305-May-23Second ClassYes Urgent 0 - 100g6NoPortal05/05/2023 09:3604-May-23###########POST-2023-000053
Dept B2509-May-2309-May-23Second ClassYes Normal 0 - 100g1NoPortal09/05/2023 08:0405-May-23###########POST-2023-000055
Dept B1109-May-2309-May-23Second ClassYes Normal 0 - 100g1NoPortal09/05/2023 08:4305-May-23###########POST-2023-000056
Dept C109-May-2309-May-23Second ClassYes Normal 0 - 100g2NoPortal09/05/2023 08:5005-May-23###########POST-2023-000057
Dept A509-May-2309-May-23Second ClassYes Urgent 0 - 100g5NoPortal09/05/2023 09:1005-May-23###########POST-2023-000058
Dept A409-May-2309-May-23Second ClassYes Urgent 0 - 100g4NoPortal09/05/2023 09:1505-May-23###########POST-2023-000059
Dept C510-May-2310-May-23Second ClassYes Urgent 0 - 100g10NoPortal10/05/2023 08:1409-May-23###########POST-2023-000060
Dept C110-May-2310-May-23Second ClassYes Urgent 0 - 100g4NoPortal10/05/2023 08:2409-May-23###########POST-2023-000061
Dept D210-May-2310-May-23AirmailYes£12.58Urgent  2NoPortal10/05/2023 08:3409-May-23###########POST-2023-000062
Dept D410-May-2310-May-23Second ClassYes Urgent 0 - 100g8NoPortal10/05/2023 08:5210-May-23###########POST-2023-000063
Dept D410-May-2310-May-23Second ClassYes Urgent 0 - 100g8NoPortal10/05/2023 08:5810-May-23###########POST-2023-000064
Dept A511-May-2311-May-23Second ClassYes Urgent 0 - 100g5NoPortal11/05/2023 07:3910-May-23###########POST-2023-000066
Dept B111-May-2311-May-23Second ClassYes Urgent 0 - 100g8NoPortal11/05/2023 08:3811-May-23###########POST-2023-000068
Dept A511-May-2311-May-23Second ClassYes Urgent 0 - 100g5NoPortal11/05/2023 10:4111-May-23###########POST-2023-000069
Dept A112-May-2312-May-23Second ClassYes Urgent 0 - 100g1NoPortal12/05/2023 07:5211-May-23###########POST-2023-000071
Dept D512-May-2312-May-23Second ClassYes Urgent 0 - 100g10NoPortal12/05/2023 08:0011-May-23###########POST-2023-000072
Dept D112-May-2312-May-23Second ClassYes Urgent 0 - 100g2NoPortal12/05/2023 08:0611-May-23###########POST-2023-000073
Dept B112-May-2312-May-23Second ClassYes Urgent 0 - 100g1NoPortal12/05/2023 08:1712-May-23###########POST-2023-000074
1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
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.           

View solution in original post

6 REPLIES 6
v-stephen-msft
Community Support
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.           

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...
SACooper_0-1684330197431.png

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

 

SACooper_0-1687533629901.png

hoping its a quick fix?

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

Top Solution Authors