How to add a calculated Column with SumIf function that takes values into account of another table

[Jelse]

Hello,

I'm new to PowerBI and I'm having a hard time to expand a table "Leverancier - Total Ordering" with a new column where every row/cell is a SUMIF of the column "Ordering" of another table "REVEX-TOTAAL". The cell/row of this new column is the SUM of all the orders of a certain supplier. The supplier is been set up as "Leverancier" (column) in tables "Leverancier - Total Ordering" & "REVEX-TOTAAL".

I want to use this as a next step for something else.

Can someone help me please? 🙂

Overview Tables

Table "Leverancier" where I want to add a SUMIF from the column "Ordering" of the table "REVEX-TOTAAL" if the "Leverancier" matches

Part of Table "REVEX-TOTAAL"

[Anonymous · Accepted solution]

HI @Jelse,

I'd like to suggest you use sumx function with current table value as condition to filter and aggregate another table records.

formula =
SUMX (
    FILTER (
        'REVEX-TOTAAL',
        'REVEX-TOTAAL'[Leverancier]
            = EARLIER ( 'Leverancier - Total Ordering'[Leverancier] )
    ),
    [Ordering]
)

SUMX function (DAX) - DAX | Microsoft Learn

EARLIER, EARLIEST – DAX Guide - SQLBI

Regards,

Xiaoxin Sheng

[Jelse]

It works! Thank you!