Forum Discussion

griffinst's avatar
griffinst
Icon for Helper I rankHelper I
4 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
    4 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 )
cengizhanarslan's avatar
cengizhanarslan
Icon for Super User rankSuper User
4 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 )