MEasure to take difference

[ianyyz]

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

[v-yiruan-msft · Accepted solution]

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] - _amount

2. 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] ) - _amount

Best Regards

[ianyyz]

anybody???

[ianyyz]

https://docs.google.com/spreadsheets/d/14kA496E2Zc8LStem-DB9RecXkulJVtzG/edit?pli=1#gid=752674320

data above