subtract value from different rows

[Anonymous]

hi all, i have seen a few answers around my question, but i cannot seem to use them, so am asking here.

below is an example of my data.

Group L1000, incorporates L2000 Amount, for all given TimeLines (records), i have highlighted December 2017 to explain my figures. L1000 always includes L2000 amounts for the same TimeLine.

so, for L1000 December 2017 i woudl like to show 1105 (which is 1303 minus 198).

there is a little more to this, but for now, solving the above, should help

thanks in advanced

[mgallacher]

Have you tried using quick measures? 

[Anonymous]

i just did thanks, and it nearly got me there. the row amounts are perfect, but the total is messing up now. take a look.

the total should be £178,612

thanks

[v-xjiin-msft · Accepted solution]

Hi @Anonymous,

Per my understand about Measure and Grand Total in Power BI. The Measure is based on Row Content, so it can use the conditions in SWITCH() function. However the Grand Total is based on entire Table Content, it cannot check each conditions in SWITCH(). It also means that the Grand Total is just sum on CALCULATE(SUM('TFINANG'[Amount])) not the values of measure.

To get the right Grand Total, you can create a new table with Measure values as its row content. Like:

New Table Adj Amount =
SUMMARIZE (
    TFINANG,
    TFINANG[Group],
    TFINANG[TimeLine],
    "Adj", IF (
        TFINANG[Group] = "L10000",
        SUM ( TFINANG[Amount] )
            - CALCULATE ( SUM ( TFINANG[Amount] ), TFINANG[Group] = "L20000" ),
        SUM ( TFINANG[Amount] )
    )
)

Thanks,
Xi Jin.

[Anonymous]

hi Xi,

i wanted to add another column to the new table. The column is just a description for the Group, however, i have lost the accurate Adj figures. is there something else i need to do, ta

[Anonymous]

thanks

worked a treat

[Anonymous]

thanks

worked a treat