Forum Discussion

griffinst's avatar
griffinst
Icon for Helper I rankHelper I
5 months ago
Solved

SumX

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...
Need Help
cengizhanarslan's avatar
cengizhanarslan
Icon for Super User rankSuper User
5 months ago

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 )