Forum Discussion

mnb_dk's avatar
mnb_dk
Helper I
3 years ago
Solved

Add TOTAL column within table

Hi

I have a table and would like to add a total column as a column in between the values.

All values needs to be summarized except colum with value 2220. 

 

See screenshot below. All values from column 1110, 1111, 1112, 1120, 1121, 1140, 1141, 1142 needs to be summarized in a new column called TOTAL (yellow). 

 

I have made a new column which summarizes the values, but when I insert this new calculated column it is not shown in the table header.

 

  • tamerj1's avatar
    tamerj1
    3 years ago

    mnb_dk 
    please follow the power query transformation details in the sample file.

    Total Amount = 
SUMX ( 
    VALUES ( G_L_Amount_Numbers[Account] ),
    IF ( 
        G_L_Amount_Numbers[Account] = "Total",
        CALCULATE ( 
            SUM ( 'Table'[Amount] ), 
            G_L_Amount_Numbers[Account] <> "2220", 
            ALL ( G_L_Amount_Numbers[Index] ) 
        ),
        SUM ( 'Table'[Amount] )
    )
)

     

7 Replies

  • mnb_dk's avatar
    mnb_dk
    Helper I
    3 years ago

    The data is sensitive 😞

    This is how the table is set up:

    • tamerj1's avatar
      tamerj1
      Community Champion
      3 years ago

      mnb_dk 
      Difficult to guess without sample data but could it be something like this?

      Total Amount = 
SUMX ( 
    VALUES ( G_L_Amount_Numbers[G_L_Amount_No] ),
    IF ( 
        G_L_Amount_Numbers[G_L_Amount_No] = "Total",
        CALCULATE ( 
            SUM ( 'Table'[Amount] ), 
            G_L_Amount_Numbers[G_L_Amount_No] <> "2220", 
            ALL ( G_L_Amount_Numbers[Index] ) 
        ),
        SUM ( 'Table'[Amount] )
    )
)
      • mnb_dk's avatar
        mnb_dk
        Helper I
        3 years ago

        Thanks for taking your time to help.

        For some reason my posts keep getting deleted.

        I would like to add sample data, but there is no way I can add it here.

        Can you use below?

         

        Document Account Amount
        332211 1110 100
        443322 1220 400
        554433 2220 300
        776655 1450 200
        665544 1110 400
        998877 1450 900
        887766 1450 600
        334466 1110 200