Subtotal Table on multiple columns based on 1 primary column

[kd3975_odi]

Hi,

I am new to Power BI and I am trying to subtotal a number of columns based on 1 primary column.

The table structure is as follows with the primary column being the ContractID and the subtotals need to be added to the

ContractValue

EstInvoiceValue

ActInvoiceValue

DonInvOpenValue

columns.

This is my data table:

Dept

ContractID

PorjectNo

ProgOfficer

PName

PStartDate

PEndDate

PStatus

AccountCode

CompanyName

AccountName

ContractNo

ContractValue

InvoiceStatus

ProjectCurrency

DonorInvCurrency

EstimatedInvoiceDate

EstInvoiceValue

EstInvoiceDesc

DonorInvoiceNo

ActualInvoiceDate

ActInvoiceValue

InvoiceBalance

DonInvOpenValue

DonorInvoiceStatus

0001

9300

Project1

Joe Bloggs

Sample Project 1

30/05/2016

05/06/2017

Projectinprogress

C0001

Donor ABC

Donor ABC London

ABC0001

1,000

BALANCE TO BE INVOICED

3

GBP

30/05/2016

750

Upfront

A10000

30/05/2016

750

-

750

O

0001

9300

Project1

Joe Bloggs

Sample Project 1

30/05/2016

05/06/2017

Projectinprogress

C0001

Donor ABC

Donor ABC London

ABC0001

1,000

BALANCE TO BE INVOICED

3

GBP

05/06/2017

250

On Completion

-

250

0002

9301

Project2

Joe Bloggs

Sample Project 2

01/01/2016

30/04/2016

Projectinprogress

C0002

Donor XYZ

Donor XYZ New York

XYZ0001

5,000

BALANCE TO BE INVOICED

3

GBP

30/04/2016

3,500

Fees

-

3,500

0002

9301

Project2

Joe Bloggs

Sample Project 2

01/01/2016

30/04/2016

Projectinprogress

C0002

Donor XYZ

Donor XYZ New York

XYZ0001

5,000

BALANCE TO BE INVOICED

3

GBP

30/04/2016

1,500

Reimbursables

-

1,500

I have searched the internet and could not find a solution so hoping this method will provide me the information I need.

Thanks

[Anonymous · Accepted solution]

Hi @kd3975_odi,

You can directly drag these columns to matrix to show the summary result or use SUMMARIZE function to get the summary table.

Table formula:

Table = SUMMARIZE(Sheet10,Sheet10[ContractID ],"Total ActInvoiceValue",SUM(Sheet10[ActInvoiceValue ]),"Total EstInvoiceValue",SUM(Sheet10[EstInvoiceValue ]),"Total InvoiceBalance",SUM(Sheet10[InvoiceBalance ]))

Regards,

Xiaoxin Sheng