cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

## Multiple Tables with Allmost same columns

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

1 ACCEPTED SOLUTION
Super User

@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 )

)

Super User

@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 )

)

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors