Multiplying Row-by-Row in a Table

[CupidC]

Hello, I'm seeking assistance on how to perform row-by-row multiplication within a table, (measure or calculated column) similar to the SUMX function, but instead of iterating through each row to sum, I'm looking to multiply each row in a table.

For instance, considering the table below where the first column represents the month, the second column represents the interest rate, and the third column represents the cumulative interest rate. To calculate the cumulative interest rate, for month 3, we need to multiply the interest rate of the first month by the interest rate of the second month, and then by the interest rate of the third month.

Any assistance or insights on how to achieve this would be greatly appreciated. Thank you in advance for your help.

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a measure.

PRODUCTX function (DAX) - DAX | Microsoft Learn

WINDOW function (DAX) - DAX | Microsoft Learn

expected result measure: = 
CALCULATE (
    PRODUCTX ( Data, Data[Interest Rate] ),
    WINDOW (
        1,
        ABS,
        0,
        REL,
        SUMMARIZE ( ALL ( Data ), Data[Month], Data[Interest Rate] ),
        ORDERBY ( Data[Month], ASC )
    )
)

[vicky_ · Accepted solution]

Try the following measure:

Cumulative Rate = PRODUCTX(FILTER(ALL('Table'), 'Table'[Month] <= EARLIER('Table'[Month])), 'Table'[Interest Rate])

Assuming that the Month column is unique (or at least, you can find a way to make it so that it's unique by combining it with a year number or you can use some sort of index column instead).

[vicky_ · Accepted solution]

Try the following measure:

Cumulative Rate = PRODUCTX(FILTER(ALL('Table'), 'Table'[Month] <= EARLIER('Table'[Month])), 'Table'[Interest Rate])

Assuming that the Month column is unique (or at least, you can find a way to make it so that it's unique by combining it with a year number or you can use some sort of index column instead).

[Ahmedx]

pls try this

[CupidC]

Hi Ahmedx, thanks for your help, but the cummulative rate does not seem to be correct, for example year 5 should be 1.638... anyway i can modify the formula to make it work?

[Ahmedx · Accepted solution]

sorry I forgot to add a column 'Table'[Intere Rate]

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a measure.

PRODUCTX function (DAX) - DAX | Microsoft Learn

WINDOW function (DAX) - DAX | Microsoft Learn

expected result measure: = 
CALCULATE (
    PRODUCTX ( Data, Data[Interest Rate] ),
    WINDOW (
        1,
        ABS,
        0,
        REL,
        SUMMARIZE ( ALL ( Data ), Data[Month], Data[Interest Rate] ),
        ORDERBY ( Data[Month], ASC )
    )
)