How to multiple sum to measure with each value of column

[ashish_18]

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!!

[ahadkarimi · Accepted solution]

@ashish_18 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'))

[ahadkarimi]

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

you can download PBIX file here.

[ashish_18]

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. 

[ahadkarimi]

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

you can download the new version from here.

[ashish_18]

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

[ahadkarimi]

@ashish_18 , there are two measures.

TotalBurnRate = SUM(MyTable[BurnRate])

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

[ashish_18]

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

[ahadkarimi · Accepted solution]

@ashish_18 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'))

[ashish_18 · Accepted solution]

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

[ahadkarimi]

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

[ahadkarimi]

@ashish_18 is there BurnRate in New table?

can you share your Data like below?

[ashish_18]

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

[Irwan]

hello @ashish_18 

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.

[ashish_18]

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.

[Irwan]

hello @ashish_18 

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.

[ashish_18]

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.