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 together

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

3.  Calculation should equal 814

 

 

metricdate_typedate_valuedate_yeardate_mnthvaluevalue2
SilverYTD20262026125177687.7883
GoldYTD20262026136978933.6245
BronzeYTD2026202619760584.8406
PlatinumYTD2026202615867948.8928
TOTALYTD20262026177782811.4372
GoldYTD20252025142170788.2152
TOTALYTD20252025188108739.1471
SilverYTD20252025128056695.0274
BronzeYTD20252025110721535.6867
PlatinumYTD2025202517161927.6576
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 )

8 Replies

  • cengizhanarslan's avatar
    cengizhanarslan
    Super User
    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
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 months ago

    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.

  • Ahmedx's avatar
    Ahmedx
    Super User
    3 months ago

    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"
        )
    )

     

     

  • V-yubandi-msft's avatar
    V-yubandi-msft
    Community Support
    3 months ago

    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.