Forum Discussion
DAX to sum column based on another table
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.
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
3 Replies
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
v-cherch-msft thank you so much! The formulas above both worked perfectly!
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!!
