Forum Discussion
Solved
MEasure to take difference
highlighted 1786 in the pivot as an example
trying to see if its possible to take the difference between grand total of 118 minus the amount for that security ID found in the "summary" tab (table 1)
if i were doing this via formulas, id do a lookup based on security ID and return column A of the "summary" tab...then have another column take the difference between the lookup column and the grand total column
Hi ianyyz ,
Assume that you have two tables 'pivot' and 'summary' in Power BI, you can follow the steps below to get it:
1. Create a calculated column in the table 'pivot'
Column = VAR _amount = CALCULATE ( SUM ( 'summary'[Amount] ), FILTER ( 'summary', 'summary'[sec id] = 'pivot'[sec id] ) ) RETURN 'pivot'[Grand Total] - _amount2. Or create a measure as below and create a table using the field 'pivot'[sec id] and this new measure
Measure = VAR _amount = CALCULATE ( SUM ( 'summary'[Amount] ), FILTER ( 'summary', 'summary'[sec id] = SELECTEDVALUE ( 'pivot'[sec id] ) ) ) RETURN SUM ( 'pivot'[Grand Total] ) - _amountBest Regards
3 Replies
anybody???
Hi ianyyz ,
Assume that you have two tables 'pivot' and 'summary' in Power BI, you can follow the steps below to get it:
1. Create a calculated column in the table 'pivot'
Column = VAR _amount = CALCULATE ( SUM ( 'summary'[Amount] ), FILTER ( 'summary', 'summary'[sec id] = 'pivot'[sec id] ) ) RETURN 'pivot'[Grand Total] - _amount2. Or create a measure as below and create a table using the field 'pivot'[sec id] and this new measure
Measure = VAR _amount = CALCULATE ( SUM ( 'summary'[Amount] ), FILTER ( 'summary', 'summary'[sec id] = SELECTEDVALUE ( 'pivot'[sec id] ) ) ) RETURN SUM ( 'pivot'[Grand Total] ) - _amountBest Regards
