How to multiply the columns and sum the total

[Anonymous]

Hi guys,

I am new here and I need a little help.

ID	Multiplier	Measure 2	Measure 3
163884	1
163958	2	1	2
163962	2	1	2
164023	2	1	2
164024		1
Total	7	4	28

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

[Stachu · Accepted solution]

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

[Thim · Accepted solution]

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.

Hope this will help.

[Anonymous]

Thank you guys, you are amazing!

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

[themistoklis · Accepted solution]

@Anonymous

Try this formula

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

[Thim · Accepted solution]

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.

Hope this will help.

[Stachu · Accepted solution]

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