Sum of Measure on rows filtered by other Measure

[Anonymous]

Hi,

I know this might me difficult, so I''ll try my best to explain.

Basically I have a Table with some Data fields that looks a bit like this:

Product ID	Views Total	Conversions	Measure CR	Measure Target Views	Selling Price	Measure Filter
1	500	50	10%	1000	50	A
2	300	60	20%	1000	20	A
3	450	10	2.222%	1000	10	A
4	400	22	5.5%	1000	40	B
Total	1650	144	8.72727%	1000	120	2

What I want is the the calculation

Target Views * Conversion Rate * Selling Price 

from every row that has Filter = "A", summed up into one final number.

In this case, it would calculate

50*0,1*50 = 250

60*0,2*20 = 240

10*0,02222*10 = 2,222

______________________

492.222

What I did to solve this is to create a new Measure

Measure 1 = CALCULATE([Target Views] * [Conversion Rate] * [Selling Price], Filter(Table,[Filter]="A"))

The problem with this is that it will work with the overall conversion rate, the average of Target Views and the Sum of Selling Price, which gives a different outcome.

I hope I was clear enough 

Thank you for any help!!

Leon

[Anonymous · Accepted solution]

@Anonymous -

To iterate over the table, you want to use SUMX. That will calculate row-by-row and then sum the values. Something like:

Measure 1 = SUMX(Filter(Table,[Filter]="A"),[Target Views] * [Conversion Rate] * [Selling Price])

Cheers!

Nathan

[Anonymous]

---

@Anonymous wrote:

@Anonymous -

To iterate over the table, you want to use SUMX. That will calculate row-by-row and then sum the values. Something like:

Measure 1 = SUMX(Filter(Table,[Filter]="A"),[Target Views] * [Conversion Rate] * [Selling Price])

Cheers!

Nathan

---

Thanks nathan, that worked perfectly.

Cheers

Leon