Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Recurring product of same column

Hi, I would like to know how to create just like the output column as given (its a sample, numbers in input column are not distinct and not in an order). I would like to know how we can create the same logical steps for output column for both calculated column and Measure function (here the output column rows = current column row value*previous output column row only and so on for subsequent rows in output column )? 

Column InputColumn Output
22
24
312
448
5240
61440
57200
536000
7252000
71764000

 

Also Please note I also have a column with distinct values which is the index column (1,2,3,4,5,6,7,8). But preferrably I would like to use a formula without considering the Index column. I will be glad if any solution/hint for this logic.

 

Thank you in advance.

 

Need Help
Show and Tell
Tips and Tricks

11 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      I tried this but I am getting the input column as the output in the "output column"

       
      Dynamic Index =
      VAR CurrentRow = MIN('Cash Flows'[Rows])
         Var _Result =  PRODUCTX(
              FILTER('Cash Flows', 'Cash Flows'[Rows] <= CurrentRow),
              'Cash Flows'[Dummy Index]
          )
          RETURN
      _Result
      • Ahmedx's avatar
        Ahmedx
        Icon for Super User rankSuper User
        3 years ago

        I did not do this, look, I created a new column - index, and then another calculated column.

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi Anonymous 

    Better with calculated column, try like:
    Output =
    PRODUCTX(
        FILTER(
            Data,
            Data[Input]<EARLIER(Data[Input])
        ),
        Data[Input]
    )
  • ppm1's avatar
    ppm1
    Icon for Solution Sage rankSolution Sage
    3 years ago

    Here's one way to do it in the query editor with a recursive funciton (and not using the Index column).  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLSAeJYHQjLBMwyBrIMIYImIEELMNMUpNLEAMw2A6kwgXJAEuZGBgiOsZkBlGcO0mNqhMw1NDczAfNjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column Input" = _t, #"Column Output" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column Input", Int64.Type}, {"Column Output", Int64.Type}}),
    InputList = #"Changed Type"[Column Input],
    Custom1 = List.Accumulate(InputList,
    {1},
(state, current) => state & {List.Last(state)*current} ),
    Custom2 = Table.FromColumns(Table.ToColumns(#"Changed Type") & {List.RemoveFirstN(Custom1, 1)})
in
    Custom2

     

     

    Pat

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi, But how to input in this a column which already exist in a table (calculated column)?

  • Adamboer's avatar
    Adamboer
    Icon for Responsive Resident rankResponsive Resident
    3 years ago

    Based on the DAX formula you provided, it looks like you are trying to calculate a product of a column based on a specific condition. However, it is resulting in empty rows in the Column Sample due to the presence of the 'Dummy Index' column.

    To avoid this, you can try modifying your formula by replacing 'Cash Flows'[Dummy Index] with a column that has non-blank values. For example, if you have a column named 'Values' that has non-blank values, you can replace 'Cash Flows'[Dummy Index] with 'Cash Flows'[Values] in the formula.

    Additionally, it is important to ensure that the 'Rows' column in your 'Cash Flows' table is set up correctly as it is being used in the WINDOW function.

    Here's the modified formula:

    Column sample = IF ( HASONEVALUE ( 'Cash Flows'[Rows] ), CALCULATE ( PRODUCTX ( 'Cash Flows', 'Cash Flows'[Values] ), WINDOW ( 1, ABS, 0, REL, ALL ( 'Cash Flows'[Rows] ), ORDERBY ( 'Cash Flows'[Rows], ASC ) ) ))

    I hope this helps. Let me know if you have any further questions.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi, the values in dummy index column is Values (decimals)  like 100, 1,1,1, 0.99, 1.02 etc but now I am facing this issue also of memory being full while running it even when I am moving the columns (Rows containing index values, Dummy Index containing calculated values of random numbers) and maybe that is why it is resulting in empty columns is their a work around ?

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    I have tried using this , it should work logically but the calculated column dummy index with random numbers is resulting in empty rows in Column Sample

    Column sample =
    IF (
        HASONEVALUE ( 'Cash Flows'[Rows] ),
        CALCULATE (
            PRODUCTX ( 'Cash Flows', 'Cash Flows'[Dummy Index] ),
            WINDOW ( 1, ABS, 0, REL, ALL ( 'Cash Flows'[Rows] ), ORDERBY ( 'Cash Flows'[Rows], ASC )
        )
    ))