SumX

[griffinst]

I'm trying to do a SUMX in power BI to replicate a SUMPRODUCT calculation in excel.

1.  Need to multiply "value" from 2025 to "value2" from 2026 for each "metric" except TOTAL, then sum all of those together

2.  Divide by the "TOTAL" "2025" "value"

3.  Calculation should equal 814

metric	date_type	date_value	date_year	date_mnth	value	value2
Silver	YTD	2026	2026	1	25177	687.7883
Gold	YTD	2026	2026	1	36978	933.6245
Bronze	YTD	2026	2026	1	9760	584.8406
Platinum	YTD	2026	2026	1	5867	948.8928
TOTAL	YTD	2026	2026	1	77782	811.4372
Gold	YTD	2025	2025	1	42170	788.2152
TOTAL	YTD	2025	2025	1	88108	739.1471
Silver	YTD	2025	2025	1	28056	695.0274
Bronze	YTD	2025	2025	1	10721	535.6867
Platinum	YTD	2025	2025	1	7161	927.6576

[cengizhanarslan · Accepted solution]

Please try the measure below:

SUMPRODUCT Result = 
VAR _2025Rows =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE (
                FactTable,
                FactTable[metric]
            ),
            "Val2025",
                CALCULATE ( MAX ( FactTable[value] ) )
        ),
        FactTable[date_year] = 2025,
        FactTable[metric] <> "TOTAL",
        ALL ( FactTable )
    )

VAR _Total2025 =
    CALCULATE (
        MAX ( FactTable[value] ),
        FactTable[metric] = "TOTAL",
        FactTable[date_year] = 2025,
        ALL ( FactTable )
    )

VAR _WithVal2026 =
    ADDCOLUMNS (
        _2025Rows,
        "Val2026",
            CALCULATE (
                MAX ( FactTable[value2] ),
                TREATAS ( { [metric] }, FactTable[metric] ),
                FactTable[date_year] = 2026,
                ALL ( FactTable )
            )
    )

VAR _Result =
    SUMX ( _WithVal2026, [Val2025] * [Val2026] )

RETURN
    DIVIDE ( _Result, _Total2025 )

[V-yubandi-msft]

Hi @griffinst ,

May I know if your issue has been resolved, or if you still need any additional details. Please let us know, and we’ll be glad to assist further.

Thank you.

[V-yubandi-msft]

Hi @griffinst ,

@cengizhanarslan , @Ashish_Mathur , and other contributors have shared the correct measure, and it is returning the expected results. Please review it and let us know if you need any further assistance from our side.

Thank you for your valuable response @Ahmedx ,@ThxAlot ,@cengizhanarslan ,@Ashish_Mathur .

[Ahmedx]

pls try this will work quickly

final = 
VAR BaseTable =
    FILTER(
        VALUES(DATA[metric]),
        DATA[metric] <> "total"
    )

RETURN
    DIVIDE(
        SUMX(
            BaseTable,
            CALCULATE(
                SUM(DATA[value]),
                DATA[date_year] = 2025
            )
            *
            CALCULATE(
                SUM(DATA[value2]),
                DATA[date_year] = 2026
            )
        ),
        CALCULATE(
            SUM(DATA[value]),
            DATA[date_year] = 2025,
            DATA[metric] = "Total"
        )
    )

 

[ThxAlot · Accepted solution]

Easy enough,

[cengizhanarslan · Accepted solution]

Please try the measure below:

SUMPRODUCT Result = 
VAR _2025Rows =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE (
                FactTable,
                FactTable[metric]
            ),
            "Val2025",
                CALCULATE ( MAX ( FactTable[value] ) )
        ),
        FactTable[date_year] = 2025,
        FactTable[metric] <> "TOTAL",
        ALL ( FactTable )
    )

VAR _Total2025 =
    CALCULATE (
        MAX ( FactTable[value] ),
        FactTable[metric] = "TOTAL",
        FactTable[date_year] = 2025,
        ALL ( FactTable )
    )

VAR _WithVal2026 =
    ADDCOLUMNS (
        _2025Rows,
        "Val2026",
            CALCULATE (
                MAX ( FactTable[value2] ),
                TREATAS ( { [metric] }, FactTable[metric] ),
                FactTable[date_year] = 2026,
                ALL ( FactTable )
            )
    )

VAR _Result =
    SUMX ( _WithVal2026, [Val2025] * [Val2026] )

RETURN
    DIVIDE ( _Result, _Total2025 )

[Ashish_Mathur]

Hi,

Please share the download link of the MS Excel file with your SUMPRODUCT() formula already written there.  I will convert that logic into DAX measures.

[griffinst]

result should be 814 not 10.1% sorry for the mistake.

[jgeddes]

I am not sure I see how your calculation equals 10.1%, but here is a measure you can play with...

Measure = 
var _total = 
MINX(
    FILTER(
        ALL('Table'), 
        'Table'[date_year] = 2025 && 'Table'[metric] = "TOTAL"
    ), 
    [value]
)
var _calc = 
SUMX(
    SUMMARIZE(
        FILTER(
            'Table', 
            'Table'[metric] <> "TOTAL"
        ), 
        'Table'[metric], 
        "__v1", 
        MINX(
            FILTER(
                'Table', 
                'Table'[date_year] = 2025
            ), 
            [value]
        ), 
        "__v2", 
        MINX(
            FILTER(
                'Table', 
                'Table'[date_year] = 2026
            ), 
            [value2]
        )
    ),
    [__v1] * [__v2]
)
var _result =
IF(
    SELECTEDVALUE('Table'[metric]) <> "TOTAL",
    DIVIDE(
        _calc,
        _total,        
        0
    ),
    BLANK()
)
RETURN
_result