SUMX for Excel's Sum Product

[crln-blue]

Hello everyone!

I posted yersterday about the SUMX for Sum Product and on some data, it was fine. But when I use it in the data I need, it does not give the result I intened to. I've been checking how and I can't seem to make it correct.. 😞

My goal is to imitate Excel's Sum Product. I use Sum Product on the number of Unique PRs for each Category (C2:C3) to the values of each category in each Step (these are average of Values column). The Total rows in the screenshot below is where the Sum Product is.

My data is like this:

Order ID	Category	PR Number	Status	Values	(other fields)...

Through search, to translate Sum Product to Power BI, we use the SUMX function. 

So first, I created a measure to get the average of the values. And then get the measure for unique number of PRs and then SUMX them both. 

So these are my measures and how it is when applied in the matrix:

# of unique prs = DISTINCTCOUNTNOBLANK('for w test'[PR])

My measure for unique PRs displays just like what is on my excel.

Below is for getting the average. (I made a measure to be able to use for sum product but I'm aware too that it can be done by just dragging the Values column and applying average aggregation)

step avg = AVERAGE('for w test'[Value])

With this, my average measure is what is expected.

This is for my totals (the sum product in excel):

sum product = 

VAR _sumprod = SUMX('for w test', [step avg] * [# of unique prs])

RETURN _sumprod

Unfortunately, it is not what I was expecting.. 😞

What would be the correct way to fix this? I'm thinking that maybe I should have add a row context or something but I'm not sure how and where..

Here is a sample pbix that I'm currently testing it on.
https://drive.google.com/file/d/1jcF-WI9aqjnncVBr-tLbj7-TRl-M71M8/view?usp=sharing

Would greatly appreaciate all the help huhu. Thanks a bunch!

[crln-blue]

Hello everyone,  after multiple tests, I think I got it by using the VALUES syntax on the table argument of the SUMX function. So my sumx formula now is : SUMX( VALUES([for w test]'Category'), [step avg] * [# of unique prs]. I think this syntax makes the row by row computation filter by category. And with that, the Sum Product that I was expecting is applied. Still testing it though.