March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
Solved! Go to 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 )
)
@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 )
)
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
91 | |
90 | |
83 | |
73 | |
49 |
User | Count |
---|---|
167 | |
149 | |
98 | |
73 | |
57 |