Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Multiply rows

Hi everybody!

 

I'm in the process of calculating a YTD Inflation. For this, I have a table of Inflation with Country, Month, GrowthPercentage. The calculation for this is:

For the months of the selected period sum 1 to each growth, multiply all of them, and subtract 1 to the result.

 

Example: 0.03 >> 1.03

1.03*1.12*1.18*1.01 = 1.37

1.37 >> 0.37 >> 37%

 

How can I perform this? Because there is SUM and SUMX function, but here what I need is a MULT funcion or something to multiply. Any ideas?

 

Thanks in advance.

 

General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    Hi Anonymous

     

    If I understand you correctly I think the PRODUCT fucntion may probably work.  If you are happy with the essence of the function, it can be extended to work with your data.

     

    Result = 
VAR IndexColumn = Table3[Index]
RETURN CALCULATE(
        PRODUCT(
            'Table3'[Rate]
            ),
            FILTER(
                'Table3',
                'Table3'[Index]<=IndexColumn )
               )

     

2 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    Hi Anonymous

     

    If I understand you correctly I think the PRODUCT fucntion may probably work.  If you are happy with the essence of the function, it can be extended to work with your data.

     

    Result = 
VAR IndexColumn = Table3[Index]
RETURN CALCULATE(
        PRODUCT(
            'Table3'[Rate]
            ),
            FILTER(
                'Table3',
                'Table3'[Index]<=IndexColumn )
               )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      So many thanks Phil_Seamark!

       

      That was exactly what I been looking for. I perfectly performed what I needed.

       

      Have a great day!