I am struggling with a calculation on Power BI.
Let's say you have two tables: 'Budget 2021' and 'Budget 2022', with the following data:
"Budget 2021" table:
Month Budget 1
January 150
February 200
March 350
"Budget 2022" table:
Month Budget 2
January 150
February 400
March 300
Therefore, I would like to have the total of each line of Budget with the result below:
Month Total Budget
January 300
February 600
March 650
I have tried different formulas below but none of them worked:
Unfortunately, when I use this formula into my real data, the results are wrong because each row of budget 1 (per month) is added with the total amount of budget 2.
So, for example, instead of having :
- 564’000 (Budget 2) + 265’444 (Budget 1) for January with a total amount of 829’444,
I will have each row of budget 1 + the total entire amount of budget 2
- Total Budget = SUMX( UNION( ADDCOLUMNS('Budget 2021', "Budget 1", Budget 2021), ADDCOLUMNS('Budget 2022', "Budget 2", Budget 2022) ), [Budget] )
I don't understand when the errors says that "ADDCOLUMNS" can not add the column "Budget 2" because it already exists)
- SUMX( UNION( ADDCOLUMNS('Budget 2021', "Budget 1", Budget 2021), ADDCOLUMNS('Budget 2022', "Budget 2", Budget 2022) ), SUM('Budget 2021'[Budget 1]) + SUM('Budget 2022'[Budget 2]) )
And I have tried others but unfortunately, all of them failed.
I have also used functions like: Userelationship, Calculate, SUMX, etc. But probably without the right structure, which didn't work as well.
I just want to calculate datas from different tables.
Is there anyone who can help me with a simple formula for beginner, please? Thanks in advance