Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
webportal
Impactful Individual
Impactful Individual

How to calculate the total of a column in a table

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

enter image description here

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

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

8 REPLIES 8
Phil_Seamark
Employee
Employee

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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.

Hi @webportal

 

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi,

 

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

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

He he! Smiley Very Happy

 

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

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!


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.