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 TimeLin...
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.

mgallacher's avatar
mgallacher
Frequent Visitor
8 years ago

Have you tried using quick measures? 

  • 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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        thanks

        worked a treat