How to calculate the total of a column in a table

[webportal]

In my table below, I've got the measure "Valor Total" as:

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?

[Phil_Seamark]

Hi @webportal

Please give this a try

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

[webportal]

Hi @Phil_Seamark

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.

[Phil_Seamark]

Hi @webportal

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

[webportal]

Hi,

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

[Phil_Seamark · Accepted solution]

HI @webportal

Lets play with some context transition 🙂

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

[webportal]

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

[Phil_Seamark]

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!

[webportal]

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