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.
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 )
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |