Measure to calculate sumproduct of unpivoted data

[mrothschild]

Link to PBIX: https://drive.google.com/file/d/1-Vb7GqAC0iBu9pBXQGg0jDS22CKCb0LG/view?usp=sharing

The desired result is a measure that from the following example data outputs 7.9% without having to create a separate pivoted table.

I've example data that starts off looking like this: 

Index	2019-3Q	2019-4Q	2020-1Q	2020-2Q	2020-3Q	2020-4Q	2021-1Q	2021-2Q	2021-3Q	2021-4Q	2022-1Q
S&P 500	1.7%	9.1%	(19.6%)	20.5%	8.9%	12.1%	6.2%	8.5%	0.6%	11.0%	(4.6%)
Burgiss Global Private Capital	1.1%	4.2%	(8.3%)	7.9%	8.7%	12.8%	8.6%	9.7%	5.3%	5.2%	0.0%

I unpivot so that I have columns of [Index], [Quarter], and [Value].  

I'm trying to create a measure that results in the sum of ([Index] = "S&P500) * ([Index] = "Burgiss Global Private Capital)

Index	2019-3Q	2019-4Q	2020-1Q	2020-2Q	2020-3Q	2020-4Q	2021-1Q	2021-2Q	2021-3Q	2021-4Q	2022-1Q
Product	0.0%	0.4%	1.6%	1.6%	0.8%	1.6%	0.5%	0.8%	0.0%	0.6%	(0.0%)

The sum of these 11 datapoints is 7.9%, which is the desired output.

[AlexandreBA]

Great solution!
I've just registered myself just to thank you.

[v-rzhou-msft · Accepted solution]

Hi @mrothschild ,

I think you can try this code to create a measure.

Measure = 
VAR _SUMMARZIE = 
SUMMARIZE (
    FILTER (
        'Quarterly Returns',
        'Quarterly Returns'[Quarter] >= "2019-3Q"
            && 'Quarterly Returns'[Quarter] <= "2022-1Q"
    ),
    'Quarterly Returns'[Quarter],
    "Percentage",
        VAR _1 =
            CALCULATE (
                SUM ( 'Quarterly Returns'[Value] ),
                FILTER (
                    'Quarterly Returns',
                    'Quarterly Returns'[Attribute] = "Burgiss Global Private Capital"
                )
            )
        VAR _2 =
            CALCULATE (
                SUM ( 'Quarterly Returns'[Value] ),
                FILTER ( 'Quarterly Returns', 'Quarterly Returns'[Attribute] = "S&P 500" )
            )
        RETURN
            _1 * _2
)
RETURN
SUMX(_SUMMARZIE,[Percentage])

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mrothschild]

Thanks!  Works exactly as needed!