topic Re: How to multiple sum to measure with each value of column in DAX Commands and Tips

How to multiple sum to measure with each value of column

Anonymous — Mon, 12 Aug 2024 20:44:59 GMT

HI Team,

I am trying to multiple Index column with measure burn rate (147,276.33) i.e 1* 147,276.33, 2*147,276.33,3*147,276.33 and so on

as a measure . DAX not allowing me multiplication of 1 column and sum of measure... Can you please advise.

Thanks in advance!!

Re: How to multiple sum to measure with each value of column

Irwan — Tue, 13 Aug 2024 00:27:09 GMT

hello Anonymous

please check if this accomodate your need.

Multiple Sum =
var _Total = CALCULATE(SUM('Table'[BurnRate]),ALL('Table'))
Return
SUMX('Table','Table'[Index]*_Total)

What you are looking is a SUMPRODUCT calculation in excel, so below is excel SUMPRODUCT to verify the result.

The calculation result in power bi matches to calculation result in excel below.

Hope this will help you.

Thank you.

Re: How to multiple sum to measure with each value of column

Anonymous — Tue, 13 Aug 2024 03:45:26 GMT

Hi Irwan ,

Thanks for looking into this. I have tried with this code but BurnRate is measure in table and your code looks for column in table. Its giving below error. ALso 'New' is table name , [Index] is regular column and BurnRate is measure.

Re: How to multiple sum to measure with each value of column

ahadkarimi — Tue, 13 Aug 2024 03:50:01 GMT

Hey Anonymous, give this a try, and if you encounter any issues, let me know.

you can download PBIX file here.

Re: How to multiple sum to measure with each value of column

Irwan — Tue, 13 Aug 2024 04:08:38 GMT

hello Anonymous

as you can see in my DAX above, _Total is a measure DAX for BurnRate sum.

so if you have separate measure for BurnRate sum, then you can directly use that measure instead of using var _Total.

here is measure for BurnRate sum:

Sum BurnRate = CALCULATE(SUM('Table'[BurnRate]),ALL('Table'))

and here is measure for SUMX index and BurnRate sum

Multiple Sum = SUMX('Table','Table'[Index]*[Sum BurnRate])

the result is same.

Hope this will help you.

Thank you.

Re: How to multiple sum to measure with each value of column

Anonymous — Tue, 13 Aug 2024 13:39:47 GMT

HI ahadkarimi Thansk for your input. I need each Index value to be mulipled by total sum of burn rate i.e.i.e 1* 147,276.33, 2*147,276.33,3*147,276.33 and so on

In you case,index value is multiplying by each value of burn rate.

Re: How to multiple sum to measure with each value of column

Anonymous — Tue, 13 Aug 2024 14:20:03 GMT

HI Irwan

IN first measure "Sum BurnRate" itself its giving error. Calculate(SUM ()) function expect regular column of table . In my case,"BurnRate" is calculated measure so this formula is not working and giving error as below. When tried with SUMX,it gives cartesian product and wrong value.

Re: How to multiple sum to measure with each value of column

ahadkarimi — Tue, 13 Aug 2024 15:24:20 GMT

Hi Anonymous, try this one and if there's any problem let me know.

you can download the new version from here.

Re: How to multiple sum to measure with each value of column

Anonymous — Tue, 13 Aug 2024 15:51:25 GMT

HI ahadkarimi can you plz share the code...Its asking to download latest pbi verison which is not allowed as of now.

Re: How to multiple sum to measure with each value of column

ahadkarimi — Tue, 13 Aug 2024 15:54:05 GMT

Anonymous , there are two measures.

TotalBurnRate = SUM(MyTable[BurnRate])

MultipliedIndexByTotalBurnRate = SELECTEDVALUE(MyTable[Index]) * CALCULATE(SUM(MyTable[BurnRate]), ALL(MyTable))

Re: How to multiple sum to measure with each value of column

Anonymous — Tue, 13 Aug 2024 16:04:56 GMT

HI ahadkarimi , [BurnRate] is calculated measure and your code expecting it as column so giving below error. If I use SUMX then it give cartecian product and wromg value

Re: How to multiple sum to measure with each value of column

ahadkarimi — Tue, 13 Aug 2024 16:09:38 GMT

Anonymous is there BurnRate in New table?

can you share your Data like below?

Re: How to multiple sum to measure with each value of column

Anonymous — Tue, 13 Aug 2024 16:12:19 GMT

Index is also regular column like BR Cost . Burn Rate is measure

Re: How to multiple sum to measure with each value of column

ahadkarimi — Tue, 13 Aug 2024 17:22:46 GMT

Anonymous Since BurnRate is a measure and not a column, the error is occurring because you’re trying to use it in a context that expects a column reference.

If there is any error, let me know.

MultipliedIndexByTotalBurnRate = SELECTEDVALUE('New'[Index]) * CALCULATE([BurnRate], ALL('New'))

Re: How to multiple sum to measure with each value of column

Anonymous — Tue, 13 Aug 2024 19:49:16 GMT

HI ahadkarimi This solution worked for me.. Thanks for your help again!!

Re: How to multiple sum to measure with each value of column

ahadkarimi — Tue, 13 Aug 2024 19:55:56 GMT

Glad it worked! Feel free to reach out by mentioning me if you need more help!