CODEWITHSUNDEEP
powerbidaxsummarize
Calle
Calle

Reputation: 153

POWER BI adding a new column to existing calculated table based on relation

I would like to ask for your help with solving following problem:

  1. I have a table called IncomeTable with three columns: Team, Income (Value), Date (month + year)
Team   Income   Date
Sales  5000$    january 2020
Marke  2000$    march 2021
  1. I have another table CostsTable with very similar structure:
Team   Costs     Date
Sales  3000$    january 2020
Marke  1000$    march 2021

What I would like to do is to create a new calculated table that looks like that:

Team     Income    Costs      Date
Sales    5000$     3000$      january 2020
Marke    2000$     1000$      march 2021

There is relation between those two tables on Date column. I tried many different formulas with Summarize, SummarizeColumns but nothing seems to solve my problem.

Could you please give me a tip how to calculate that?

Many thanks in advance.

Upvotes: 1

Views: 583

Answers (1)

davidebacci
davidebacci

Reputation: 30344

I presume your relationship should be based on Team and Date. If this is this case, the following should work.

NewTable = ADDCOLUMNS( IncomeTable,
            "Costs", CALCULATE(MIN(CostsTable[Costs]), TREATAS( SUMMARIZE(CostsTable,  CostsTable[Date], CostsTable[Team]), IncomeTable[Date], CostsTable[Team] ) ))

Upvotes: 1

Related Questions