Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

subtract value from different rows

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

 

Need Help
  • v-xjiin-msft's avatar
    v-xjiin-msft
    8 years ago

    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.

6 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        v-xjiin-msft
        Solution Sage
        8 years ago

        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.