How to calculate each line of datas from different tables on Power BI?

97 Views Asked by At

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 :

  1. 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

  1. 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)

  1. 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

0

There are 0 best solutions below