Forum Discussion

griffinst's avatar
griffinst
Helper I
3 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
    3 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 )
jgeddes's avatar
jgeddes
Super User
3 months ago

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