cancel
Showing results for
Did you mean:
Post Prodigy

## How to calculate the total of a column in a table

`Valor total = SUM('Códigos e valores'[Valor unitário])*SUM(Csv[Quant.])`

This works ok for all the rows of the table except for the grand total at the bottom, because the definition can't be quantity * value.

But how to express this in the correct way?

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft

## Re: How to calculate the total of a column in a table

Lets play with some context transition 🙂

```Valor total =
SUMX(
'Códigos e valores',
CALCULATE(SUM('Csv'[Quant.]))* 'Códigos e valores'[Valor unitário]
)```

Proud to be a Datanaut!

8 REPLIES 8
Microsoft

## Re: How to calculate the total of a column in a table

```Valor total =
SUMX(
'Códigos e valores',
'Códigos e valores'[Quant.] * 'Códigos e valores'[Valor unitário]
)```

Proud to be a Datanaut!

Post Prodigy

## Re: How to calculate the total of a column in a table

Thanks for helping.

The problem here is that the field "Quant" is on a table with many-to-one relationship with another table (Table2).

This Table2 has a relationship of one-to-many to table "Códigos e valores" where field "Valor Unitário" is located.

Therefore, SUMX doesn't work in this scenario, not even with a RELATED in the expression.

Microsoft

## Re: How to calculate the total of a column in a table

So is the Quant. column in a parent, or child table?

Proud to be a Datanaut!

Post Prodigy

## Re: How to calculate the total of a column in a table

Hi,

"Quant" is on the may side of the relationship, so I think it is a child.

Microsoft

## Re: How to calculate the total of a column in a table

Lets play with some context transition 🙂

```Valor total =
SUMX(
'Códigos e valores',
CALCULATE(SUM('Csv'[Quant.]))* 'Códigos e valores'[Valor unitário]
)```

Proud to be a Datanaut!

Highlighted
Post Prodigy

## Re: How to calculate the total of a column in a table

He he!

Not exactly that one... The following worked, so thanks anyway!

`Valor total = SUMX('Códigos e valores';CALCULATE(SUM(Csv[Quant]))*CALCULATE(SUM('Códigos e valores'[Valor unitário])))`
Microsoft

## Re: How to calculate the total of a column in a table

Nice work.  In theory, you shouldn't need the second CALCULATE but hey, if it works and the speed is all good then well done!

Proud to be a Datanaut!

Post Prodigy

## Re: How to calculate the total of a column in a table

@Phil_Seamark, you're right. No need for a second CALCULATE.

But in your expression, you miss the SUM. So this is perfect:

`Valor total = SUMX('Códigos e valores';CALCULATE(SUM(Csv[Quant]))*SUM('Códigos e valores'[Valor unitário]))`

Announcements

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors