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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dnsia
Helper II
Helper II

Sum between values of multiple columns in two tables

Hi all,

I received 2 tables (Table 1 & 2).

I need to apply the Expenses (Table 1. Expenses columns) on Table 2 as below to produce the bold columns in table 3.


1. Table 2 columns (columns: LOAD container size, laden, empty) need to look up on table 1 columns  (port, container size, status columns) and check that they have the same values to apply the correct expense - load (A, B, C) column as shown in table 3 below.

2. This process is to be repeated with same parameters except that it will now refer to the DISCHARGE column instead of LOAD column in table 2. This will produce columns expense - discharge (A, B, C).

Table 1. Expense Matrix 

Port Container Size Container Status Expense A Expense B Expense C
PORT 1 20s LADEN 130 1500 1350
PORT 1 20s EMPTY 0 600 1350
PORT 1 40s LADEN 195 2750 1350
PORT 1 40S EMPTY 0 1100 1350
PORT 2 20s LADEN 120 1000 775
PORT 2 20s EMPTY 0 400 775
PORT 2 40s LADEN 170 2000 775
PORT 2 40S EMPTY 0 800 775



Table 2. Container Inventory

Container Container Size Load Discharge LADEN EMPTY
CONT 1 40s PORT 1 PORT 2 Y N
CONT 2 40s PORT 2 PORT 1 Y N
CONT 3 20s PORT 2 PORT 1 Y N
CONT 4 40s PORT 1 PORT 2 Y N
CONT 9 20s PORT 1  PORT 2 Y N



TABLE 3. The Needed output

ContainerCont SizeLoadDischargeLADENEMPTYEXPENSE A - LOADEXPENSE B - LOADEXPENSE C - LOADEXPENSE A - DISCHARGEEXPENSE B - DISCHARGE EXPENSE C - DISCHARGE
CONT 140sPORT 1PORT 2YN195275013501702000775
CONT 240sPORT 2PORT 1YN170200077519527501350
CONT 320sPORT 2PORT 1YN120100077513015001350
CONT 440sPORT 1PORT 2YN195275013501702000775
CONT 920sPORT 1 PORT 2YN130150013501201000775


Thank you for your help! 

Regards,
Dina

3 ACCEPTED SOLUTIONS
v-eqin-msft
Community Support
Community Support

Hi @dnsia ,

According to my understand, you want to get the value from Expense Matrix[A],[B],[C] whose PORT and CONTAINER SIZE matched the same from  Container Inventory table, right?

 

You could use LOOKUPVALUE() function to add columns in Container Inventory table like this:

EXPENSE A-LOAD =
LOOKUPVALUE (
    'Expense Matrix'[Expense A],
    'Expense Matrix'[Port], [Load],
    'Expense Matrix'[Container Size], [Container Size],
    'Expense Matrix'[Container Status], "LADEN"
)
EXPENSE B-DISCHARGE =
LOOKUPVALUE (
    'Expense Matrix'[Expense B],
    'Expense Matrix'[Port], [Discharge],
    'Expense Matrix'[Container Size], [Container Size],
    'Expense Matrix'[Container Status], "LADEN"
)

My final output as shown below:

11.30.3.1.PNG

 

Here is the pbix file.

 

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

 

Best Regards,
Eyelyn Qin

View solution in original post

v-eqin-msft
Community Support
Community Support

Hi @dnsia ,

Take one of them as an example:

EXPENSE A-DISCHARGE Measure =
CALCULATE (
    MAX ( 'Expense Matrix'[Expense A] ),
    FILTER (
        'Expense Matrix',
        'Expense Matrix'[Port] = MAX ( 'Container Inventory'[Discharge] )
            && 'Expense Matrix'[Container Size] = MAX ( 'Container Inventory'[Container Size] )
            && 'Expense Matrix'[Container Status] = "LADEN"
    )
)

11.30.sum follow.PNG

Here is the pbix file.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.


Best Regards,
Eyelyn Qin

View solution in original post

v-eqin-msft
Community Support
Community Support

Hi @dnsia ,

Please try this:

Measure =
SUMX ( 'Container Inventory', [EXPENSE A-DISCHARGE Measure] )

11.30.sum.fo2.PNG

Best Regards,
Eyelyn Qin

 

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

7 REPLIES 7
v-eqin-msft
Community Support
Community Support

Hi @dnsia ,

Please try this:

Measure =
SUMX ( 'Container Inventory', [EXPENSE A-DISCHARGE Measure] )

11.30.sum.fo2.PNG

Best Regards,
Eyelyn Qin

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-eqin-msft,

Works okay now. Thank you!


Regards,
Dina 

v-eqin-msft
Community Support
Community Support

Hi @dnsia ,

Take one of them as an example:

EXPENSE A-DISCHARGE Measure =
CALCULATE (
    MAX ( 'Expense Matrix'[Expense A] ),
    FILTER (
        'Expense Matrix',
        'Expense Matrix'[Port] = MAX ( 'Container Inventory'[Discharge] )
            && 'Expense Matrix'[Container Size] = MAX ( 'Container Inventory'[Container Size] )
            && 'Expense Matrix'[Container Status] = "LADEN"
    )
)

11.30.sum follow.PNG

Here is the pbix file.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.


Best Regards,
Eyelyn Qin

Hi @v-eqin-msft , 

Thank you for your swift response.  The total seems to be wrong? Total should should as 785 but is only showing as 170.


Regards,
Dina

v-eqin-msft
Community Support
Community Support

Hi @dnsia ,

 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

 

Best Regards,
Eyelyn Qin

Hi @v-eqin-msft , 

Works fine. Thank you! Do you have a measure for this instead of calculated column because I will be dealing with thousands of data.

Regards,

Dina

v-eqin-msft
Community Support
Community Support

Hi @dnsia ,

According to my understand, you want to get the value from Expense Matrix[A],[B],[C] whose PORT and CONTAINER SIZE matched the same from  Container Inventory table, right?

 

You could use LOOKUPVALUE() function to add columns in Container Inventory table like this:

EXPENSE A-LOAD =
LOOKUPVALUE (
    'Expense Matrix'[Expense A],
    'Expense Matrix'[Port], [Load],
    'Expense Matrix'[Container Size], [Container Size],
    'Expense Matrix'[Container Status], "LADEN"
)
EXPENSE B-DISCHARGE =
LOOKUPVALUE (
    'Expense Matrix'[Expense B],
    'Expense Matrix'[Port], [Discharge],
    'Expense Matrix'[Container Size], [Container Size],
    'Expense Matrix'[Container Status], "LADEN"
)

My final output as shown below:

11.30.3.1.PNG

 

Here is the pbix file.

 

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

 

Best Regards,
Eyelyn Qin

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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