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

     

mnb_dk's avatar
mnb_dk
Helper I
3 years ago

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's avatar
tamerj1
Community Champion
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] )
    )
)