How can I reproduce Excel SumProduct in Power BI

[JGalvan]

I am new to Power BI. I use Excel's SumProduct function to calculate a weighted average. I'm trying to do this using SUMX. However, I'm getting an error message regarding "multiple columns cannot be converted to a scalar value.

[m3tr01d]

@JGalvan What kind of visual in Power BI are you trying to make?

[JGalvan]

@m3tr01d 

I will eventually create a historical line chart with the vacancy rate (also going to get weighted average for other metrics) for the "weighted" value as one "market".

[v-kelly-msft · Accepted solution]

Hi @JGalvan ,

Your expression is for a calculated table,you need to create a table using that expression,and it will work fine:

If you need a measure,using below expression；

Measure = SUMX(FILTER(ALL('Table'),'Table'[Year]=MAX('Table'[Year])),'Table'[Percentage]*'Table'[Vacrate])

And you will see:

Check my sample .pbix file attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[JGalvan]

This worked perfectly! Using the 'new table' suggestion, I was able to create an entire, historical data set on the 'new market' using the weighted averages for all the contributing submarkets.

Thank you @v-kelly-msft . I really appreciate it.

[JGalvan]

Thank you all for your input. Per my original snippet, I am trying to group by Year and get a single value (which would be repeated in every row for the year) using the aggregated data. The single value = the weighted average data point for that year.

Thank you @Ashish_Mathur . I am still unclear on this issue. What I'm trying to get is a single value (using the aggregated "weighted average" of 29 contributors) for each year. That's why, in my original post image, I was trying to group by year in order to get a single value per year. Maybe this new image will help explain what I'm trying to do. I appreciate your input.

[Ashish_Mathur]

Hi,

In a slicer, select a certain year.  Drag this measure to a card visual

Measure = SUMX(VALUES(Data[Parts]),Data[Vacrate]*Data[Percentage])

Hope this helps.

[Ashish_Mathur]

Hi,

Write the formula as a measure (not as a calculated column)

Measure = SUMX('sheet1','sheet1'[vacrate]*'sheet1'[Percentage])

Hope this helps.

[jdbuchanan71]

@JGalvan 

A straight SUMX measure using the two columns is the same as excel SUMPRODUCT.

WTD vac = SUMX ( Sheet1, Sheet1[Vacrate] * Sheet1[Percentage] )

[JGalvan]

Thank you @jdbuchanan71  - I submitted another snippet below to further explain what I'm trying to do.