DAX to sum column based on another table

[sfink22]

Hello! Trying to work out the right equation to sum a column based on another table for a budget dashboard. Here is my setup:

Table 1 has the budget info by period, like this:

Period    Budget

1            $500

2            $350

etc.

Table 2 has the actual charges accumulated YTD:

Period    ChargeName    Amount

1            Cell phone        $110

1            Lunch                $15

etc.

How do I add a column to Table 1 that sums all of the charges for Period 1 from Table 2? So I end up with this:

Period    Budget    Spent

1            $500        $125

2            $350

I would also like to add another column to Table 1 that shows cumulative spend through each period. So on the Period 1 row it would show all spend for Period 1, Period 2 row would show Period 1 + Period 2, etc.

[v-cherch-msft · Accepted solution]

Hi @sfink22

You may create calculated columns as below:

Column =
SUMX (
    FILTER ( 'Table 2', 'Table 2'[Period] = 'Table 1'[Period] ),
    'Table 2'[Amount]
)

Column 2 =
SUMX (
    FILTER ( 'Table 1', 'Table 1'[Period] <= EARLIER ( 'Table 1'[Period] ) ),
    'Table 1'[Column]
)

Regards,

Cherie

[manojsv]

Hello All,

I have the similar requirements, but i wanted to use DAX measure to calculate the total sum (335). Can you please help me in getting desired result by using DAX measure.

Appreciate any suggestions/tips!!

[sfink22]

@v-cherch-msft thank you so much! The formulas above both worked perfectly!