topic Add TOTAL column within table in DAX Commands and Tips

Add TOTAL column within table

mnb_dk — Mon, 21 Aug 2023 14:10:40 GMT

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.

Re: Add TOTAL column within table

tamerj1 — Mon, 21 Aug 2023 14:19:25 GMT

Hi mnb_dk
Please provide sample data

Re: Add TOTAL column within table

mnb_dk — Mon, 21 Aug 2023 14:23:32 GMT

The data is sensitive 😞

This is how the table is set up:

Re: Add TOTAL column within table

tamerj1 — Mon, 21 Aug 2023 14:53:05 GMT

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

Re: Add TOTAL column within table

mnb_dk — Tue, 22 Aug 2023 09:36:25 GMT

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

Re: Add TOTAL column within table

mnb_dk — Tue, 22 Aug 2023 09:37:04 GMT

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.

Re: Add TOTAL column within table

mnb_dk — Tue, 22 Aug 2023 09:37:15 GMT

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

Re: Add TOTAL column within table

tamerj1 — Tue, 22 Aug 2023 10:19:44 GMT

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