SUMPRODUCT in Matrix Visual

[danakajoel]

HI,

I have a relatively simple dataset copied below where I am seeking to replicate SUMPRODUCT from Excel by working out the difference between sub asset classes between two dates and applying a weighting. I have been able to create the measure but it is displaying oddly in the Matrix visual in Power BI desktop so I am hoping someone can point me in the direction of what I need to add:

BB Summary	Sub AA	Attribute	Value	Weight
Gilts	Gilt Long	30 June 2021	3941.19	0.25
Gilts	Gilt Long	31 May 2021	3912.91	0.25
Gilts	Gilt Short	30 June 2021	305.48	0.75
Gilts	Gilt Short	31 May 2021	304.78	0.75

The table is called "Benchmark Performance (GBP)" and I have been able to create a measure as follows:

Value % difference from Period Start GBP =

VAR STARTVALUE = CALCULATE(SUM('Benchmark Performance (GBP)'[Value]), 'Date'[Variable Date] IN { "Period Start" })

VAR ENDVALUE = CALCULATE(SUM('Benchmark Performance (GBP)'[Value]),'Date'[Variable Date] IN { "Period End" })

VAR BBPERF = DIVIDE(ENDVALUE-STARTVALUE,STARTVALUE)

VAR RESULT = SUMX('Benchmark Performance (GBP)','Benchmark Performance (GBP)'[Weight]*BBPERF)

    RETURN

RESULT

This is returning what I'd expect at Sub Asset Allocation level (SUB AA) in a visual but the wrong weighting for BB Summary level:

In the above example, I would expect the output to be Gilts 0.18+0.17% = 0.35%

Any pointers greatly appreciated

[Vera_33 · Accepted solution]

Hi @danakajoel 

try it

test= SUMX(VALUES('Benchmark Performance (GBP)'[Sub AA]),[Value % difference from Period Start GBP])

[danakajoel]

Hi Vera. Absolutely perfect. Thank you. I'd spent hours investigating and trying ISINSCOPE etc to no avail. I'll need to read up on the VALUES function to better my understanding but problem solved 🙂