Add TOTAL column within table

[mnb_dk]

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.

[mnb_dk]

The data is sensitive 😞

This is how the table is set up:

[tamerj1]

@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]

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

[tamerj1 · Accepted solution]

@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] )
    )
)

[mnb_dk]

Hi

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. Please see below post for sample data.

[mnb_dk]

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

[tamerj1]

Hi @mnb_dk 
Please provide sample data