Forum Discussion

ianyyz's avatar
ianyyz
Helper I
2 years ago
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" t...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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