Forum Discussion

mnb_dk's avatar
mnb_dk
Icon for Helper I rankHelper 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
Icon for Helper I rankHelper I
3 years ago

The data is sensitive 😞

This is how the table is set up:

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity 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
      Icon for Helper I rankHelper 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

    • mnb_dk's avatar
      mnb_dk
      Icon for Helper I rankHelper I
      3 years ago

      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's avatar
      mnb_dk
      Icon for Helper I rankHelper 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
        Icon for Community Champion rankCommunity 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] )
    )
)