cancel
Showing results for 
Search instead for 
Did you mean: 
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 🙂

Proud to be a Super User!

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
Super User
Super User

@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
Super User
Super User

@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 🙂

Proud to be a Super User!

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!