Multiple Tables with Allmost same columns

[Anonymous]

Hi All,

I have 4 tables which has the same columns (25 columns ). The 4 tables contain _ revenue, Direct cost, Travel cost and Material cost.  I want to be able to work on these tables and i have tried the below options

a) Concatenate the columns to arrive at Primary key - This is too cumbersome a process and to repeat the same process across all tables would not make sense.

b) Append the tables - Appending the tables looks fine as all columns are one below the other. However i wanted to arrive at gross profit (revenue- Direct cost- Travel cost - Material cost). I am unable to figure out how to go about.

Is there a way to write a measure in an Appended table.

regards

[d_gosbell · Accepted solution]

---

@Anonymous wrote:

Is there a way to write a measure in an Appended table.

---

There sure is, provided you have some column that lets you identify the revenue, direct cost, travel cost and material cost. If you have those specific values in some column (eg. [Account Type]) you could write something like the following: 

Revenue = CALCULATE( SUM('Appended Table'[Amount] , 'Appended Table'[Account Type] = "Revenue")

Or if you have a list of values that define revenue you could do something like the following:

Revenue = CALCULATE( SUM('Appended Table'[Amount] , 'Appended Table'[Account No] IN {1000, 1100,1200})

Or if you had a range of Account numbers you could do something like the following:

Revenue = CALCULATE( SUM('Appended Table'[Amount]
  , FILTER(VALUES('Appended Table'[Account No]) 'Appended Table'[Account No] >= 1000
  , FILTER(VALUES('Appended Table'[Account No]) 'Appended Table'[Account No] <= 1999 )

)