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
Anonymous
Not applicable

How to multiply the columns and sum the total

Hi guys,

I am new here and I need a little help.

 

IDMultiplierMeasure 2Measure 3
1638841  
163958212
163962212
164023212
164024 1 
Total7428


The DAX code of measure 3 is (Measure 3 = CALCULATE([Measure 2]*[Multiplier]))

But I needed that the measure 3 sum the total and don't multiply, the total should be 6 instead 28.

Do I make myself clear?

Thanks for the help

3 ACCEPTED SOLUTIONS
Stachu
Community Champion
Community Champion

you need to use SUMX
https://docs.microsoft.com/en-us/dax/sumx-function-dax
possibly with SUMMARIZE to get sum per ID



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

Thim
Resolver V
Resolver V

This is'nt a pretty solution, but it will work. 🙂

 

Make an extra column (i have called it "Measure 2,5") before you makre colum "Measure 3"

 

Use this Dax Expression.

Measure 2,5 = IF(Table1[ID]="Total";0;Table1[Multiplier]*Table1[Measure 2])

 

This is to get alle the results you need except the total.

 

Then make the "Measure 3" column with the following Dax Expression.

Measure 3 = IF(Table1[ID]="Total";SUM(Table1[Measure 2,5]);Table1[Measure 2,5])

 

This should give you the wanted result.Multiply.PNG

 

Hope this will help.

View solution in original post

themistoklis
Community Champion
Community Champion

@Anonymous

 

Try this formula

Measure 3 = SUMX( VALUES ( TABLE[ID] ), CALCULATE([Measure 2]*[Multiplier]) )

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Thank you guys, you are amazing!

This is a great community that I am so proud to become a member.

themistoklis
Community Champion
Community Champion

@Anonymous

 

Try this formula

Measure 3 = SUMX( VALUES ( TABLE[ID] ), CALCULATE([Measure 2]*[Multiplier]) )
Thim
Resolver V
Resolver V

This is'nt a pretty solution, but it will work. 🙂

 

Make an extra column (i have called it "Measure 2,5") before you makre colum "Measure 3"

 

Use this Dax Expression.

Measure 2,5 = IF(Table1[ID]="Total";0;Table1[Multiplier]*Table1[Measure 2])

 

This is to get alle the results you need except the total.

 

Then make the "Measure 3" column with the following Dax Expression.

Measure 3 = IF(Table1[ID]="Total";SUM(Table1[Measure 2,5]);Table1[Measure 2,5])

 

This should give you the wanted result.Multiply.PNG

 

Hope this will help.

Stachu
Community Champion
Community Champion

you need to use SUMX
https://docs.microsoft.com/en-us/dax/sumx-function-dax
possibly with SUMMARIZE to get sum per ID



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

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.