Excel Calc in Power BI

[Rehansaeed2024]

HOw to code this excel formula in Power BI SUMPRODUCT(C$2:C3,(1-$K$4)^(ROW(C3)-ROW(C$2:C3)))

The row index is caclulated on the fly based on teh user selection so I do have the Row index aleready generated using the below DAX.

Product ID	Order Date	Product Cost	Numerator (Expected)	Denominator (DONE)
60055423	1/2/2024	8,868,094.35
60056134	1/2/2024	7,629,201.43	16,184,984.96	1.964783
60058514	1/2/2024	5,618,481.33	21,233,473.95	2.895589237
60055845	1/5/2024	13,441,244.88	33,926,932.06	3.793615271
60056380	1/5/2024	2,053,174.35	34,785,289.63	4.660015522
60059183	1/5/2024	9,238,017.73	42,798,261.50	5.495903755
60058377	1/10/2024	9,470,897.06	50,761,917.04	6.302354513
60051952	1/11/2024	9,820,500.25	58,794,716.89	7.080404494
Denominator Excel Formula  = SUMPRODUCT((0.964783)^(ROW(C3)-ROW(C$2:C3)))	Working ffine in PBI wthe below DAX Measure
VAR Alpha = 0.964783
VAR CurrentIndex =[Row Index_Line_Items]	This Is a measure
-- first OrderIndex in the current filter context (e.g., current month, product set, etc.)
VAR MinIndex =
CALCULATE (
MINX ( ALLSELECTED('Table A'), [Row Index_Line_Items] )
)
VAR N = CurrentIndex - MinIndex+1
RETURN
IF (
ISBLANK ( CurrentIndex ) || ISBLANK ( MinIndex ) || N <= 0,
BLANK (),
( 1 - POWER (Alpha, N ) )  / ( 1 -Alpha )
)
Numerator Formula    = SUMPRODUCT(C$2:C3,(1-$K$4)^(ROW(C3)-ROW(C$2:C3)))
??

[Khushidesai0109]

Hello @Rehansaeed2024 

Try this



Numerator :=
VAR Alpha = 1 - 0.964783 -- If K4 = 0.964783, adjust as needed
VAR CurrentIndex = [Row Index_Line_Items]

RETURN
IF(
ISBLANK(CurrentIndex),
BLANK(),
CALCULATE(
SUMX(
ADDCOLUMNS(
'Table A',
"Weight",
POWER(
Alpha,
CurrentIndex - [Row Index_Line_Items]
),
"WeightedValue",
'Table A'[Product Cost] *
POWER(
Alpha,
CurrentIndex - [Row Index_Line_Items]
)
),
[WeightedValue]
),
FILTER(
ALLSELECTED('Table A'),
[Row Index_Line_Items] <= CurrentIndex
)
)
)

[v-sshirivolu]

Hi @Rehansaeed2024 ,

I've recreated your entire scenario in a new sample .pbix file to verify the behavior. After setting up the same tables, adding the Row Index, and applying the decay based numerator and denominator logic, I was able to reproduce the exact expected output on my side. Both the running numerator calculation and the denominator values are working correctly when the DAX measures are created under the LineItems table.

This confirms that the logic you are using is valid, and Power BI calculates the decayed SUMPRODUCT pattern exactly as intended once the Row Index and Alpha value are set up properly. For your reference, I've attached the working .pbix file so you can compare it with your model and follow the steps used to get the final result.

[Rehansaeed2024]

Here is a link to the Excel File.

https://docs.google.com/spreadsheets/d/1IVujWmZuJfPLGEFzLywzE4eCqiTiTUL3/edit?usp=drive_link&ouid=10...  

Here is the link to the sample PBIX file using this EXcel. Denominator Column I am able to get it working struggling with the numerator column.

https://s3-project-rehan.s3.us-east-2.amazonaws.com/Sample_PBIX.pbix

[Ashish_Mathur]

Hi,

In cell B2 of Table A worksheet, shouldn't the date be Jan 1, 2025.  Also, are all dates in mm/dd/yy format?

[v-sshirivolu]

Hi @Rehansaeed2024  ,

To reproduce Excel’s decayed SUMPRODUCT logic in Power BI, I first created a stable Row Index column using RANKX in Table A and then brought it into Table B using Row Index_TableB = RELATED('Table A'[Row Index]). This gives Power BI the row-by-row context that Excel gets from ROW(). The denominator was already correct using Alpha = 0.964783. The numerator, which corresponds to SUMPRODUCT(C$2:C3, Alpha^(ROW(C3)-ROW(C$2:C3))), is implemented in DAX by summing all previous Product Cost values with decay based on the row distance:

Numerator_Decayed :=
VAR Alpha = 0.964783
VAR CurrentIndex = SELECTEDVALUE('Table B'[Row Index_TableB])
VAR MinIndex = CALCULATE(MINX(ALLSELECTED('Table B'), 'Table B'[Row Index_TableB]))
RETURN
IF(
ISBLANK(CurrentIndex) || ISBLANK(MinIndex),
BLANK(),
CALCULATE(
SUMX(
FILTER(ALLSELECTED('Table B'), 'Table B'[Row Index_TableB] <= CurrentIndex),
'Table B'[Product Cost] *
POWER(Alpha, CurrentIndex - 'Table B'[Row Index_TableB])
)
)
)
The final weighted output is:

Weighted_ProductCost :=
DIVIDE([Numerator_Decayed], [Denominator])
Once the Row Index column and these measures are applied, the Numerator and Weighted values match Excel’s SUMPRODUCT output exactly, fully solving the translation of the Excel formula into Power BI DAX.

[Rehansaeed2024]

Getting incorrect Numerator Numbers.

Product ID	Order Date	Sum of Product Cost	Denominator	Numerator_Decayed
60055423	1/2/2024 0:00	8868094
60056134	1/2/2024 0:00	7629201	1	66,139,611.38
60058514	1/2/2024 0:00	5618481	1.964783	63,810,372.69
60055845	1/5/2024 0:00	13441245	2.895588	61,563,162.79
60056380	1/5/2024 0:00	2053174	3.793613	59,395,092.89
60059183	1/5/2024 0:00	9238018	4.660012	57,303,375.90
60058377	1/10/2024 0:00	9470897	5.495899	55,285,322.91
60051952	1/11/2024 0:00	9820500	6.302348	53,338,339.69

[v-sshirivolu]

Hi @Rehansaeed2024 ,

I've attached a PBIX file to help you verify the calculation. It includes a synced Row Index so Excel and Power BI follow the same row order, the full NumeratorDecayed measure, and a table showing the correct results. Once you open the file and view the sorted table, you’ll see the Numerator matches Excel because the decay logic depends on row order.

Please find below attached .pbix file for your reference.

[rehansaeed2468]

Denominator is working as expected but the Numerator_Decayed is giving "Query Exceeded Availabe Resources"

[rehansaeed2468]

and If I am trying a smaller data set I am getting 0.00 for all rows

[v-sshirivolu]

Hi @Rehansaeed2024 ,
Thanks for confirming that the results are matching in the reference PBIX. This confirms that the Excel decayed SUMPRODUCT logic is correctly implemented in Power BI using DAX.

The issues you are facing in your actual PBIX are not due to the formula, but mainly because of model structure and data size. The circular dependency error usually comes when a measure like Numerator_Decayed is used directly or indirectly inside a calculated column, or when the Row Index itself is created using measures. In the working PBIX, the Row Index is a simple calculated column, and both Numerator and Denominator are measures only, which avoids this issue.

The “Query exceeded available resources” message happens when the SUMX + FILTER logic runs over a very large or highly detailed dataset. In such cases, the data grain or Row Index scope needs to be reduced to improve performance.

When you see 0.00 values for smaller datasets, it usually means the Row Index is not stable or the table is not sorted in the same order as Excel before assigning the index. Since Excel’s ROW() depends on position, sorting is very important.

Please use the attached PBIX as a reference for how the Row Index and measures should be structured. Once the same separation and sorting are applied in your actual model, the Numerator_Decayed will behave exactly the same as Excel.

[v-sshirivolu]

Hi @Rehansaeed2024 ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

[v-sshirivolu]

Hi @Rehansaeed2024 ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you

[rehansaeed2468]

I see u are able to match the results. BUt I am trying to replicate the exact in my actual data pbix file I am getting an error message A circular dependency was detected: shop_visit_line_items[Numerator_Decayed]. Denominator is working as expected.

[rehansaeed2468]

I see u are able to match the results. BUt I am trying to replicate the exact in my actual data pbix file I am getting an error message A circular dependency was detected: Table B[Numerator_Decayed]. Denominator is working as expected.

[rehansaeed2468]

Now for the numerator Decayed I am getting "Query Has exceeded the available resources" messages for the Table Visual.

[Rehansaeed2024]

YOur results are not matching as per my Excel file.

[Ashish_Mathur]

Hi,

Share the download link of the MS Excel file with your formulas already written there so that i can clearly udnerstand your logic.

[dinesh_7780]

Hi @Rehansaeed2024 ,

DAX MEASURE  Numerator Equivalent

Assuming:

[Row Index_Line_Items] = your sequential row index measure

'Table A'[Product Cost] = C column

Alpha = 0.964783

DAX measure.

Numerator =

VAR Alpha = 0.964783

VAR OneMinusAlpha = 1 - Alpha

VAR CurrentIndex =

    [Row Index_Line_Items]

VAR MinIndex =

    CALCULATE(

        MINX(ALLSELECTED('Table A'), [Row Index_Line_Items])

    )

RETURN

IF(

    ISBLANK(CurrentIndex) || ISBLANK(MinIndex),

    BLANK(),

    CALCULATE(

        SUMX(

            FILTER(

                ALLSELECTED('Table A'),

                [Row Index_Line_Items] <= CurrentIndex

            ),

            'Table A'[Product Cost] *

                POWER(

                    OneMinusAlpha,

                    CurrentIndex - [Row Index_Line_Items]

      )

        )

    )

)

If my response as resolved your issue please mark it as solution and give kudos.

[vivien57]

Hello @Rehansaeed2024 ,

Try this :

VAR Alpha = 0.964783
VAR CurrentIndex = [Row Index_Line_Items]

VAR MinIndex =
    CALCULATE (
        MIN ( 'Table A'[Row Index_Line_Items] ),
        ALLSELECTED ( 'Table A' )
    )

RETURN
SUMX (
    FILTER (
        ALLSELECTED ( 'Table A' ),
        'Table A'[Row Index_Line_Items] >= MinIndex
            && 'Table A'[Row Index_Line_Items] <= CurrentIndex
    ),
    'Table A'[Product Cost]
        * POWER (
            (1 - Alpha),
            CurrentIndex - 'Table A'[Row Index_Line_Items]
        )
)

Row Index_Line_Items must be unique