Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
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
d_gosbell
Super User
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 )

)

View solution in original post

1 REPLY 1
d_gosbell
Super User
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 )

)

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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