locked
Running total column against 5 columns data using DAX RRS feed

  • Question

  • Hi everyone,

    I am able to get the running total calculated column against 1 and 2 columns of same table into tabular data model but while I try to calculate the running total against 4 and 5 columns its taking very long time and stuck.

    e.g.: date, col1, col2, col3, col4, col5, Amount, Running total (column names)

    data into table: 500000 records

    DAX using : Calculate(SUM([Amount]), Filter('Table', 'Table'[Date] <= Earlier('Table'[Date]) &&  'Table'[col1] <= Earlier('Table'[col1]) && 'Table'[col2] <= Earlier('Table'[col2]) &&  'Table'[col3] <= Earlier('Table'[col3]) && 'Table'[col4] <= Earlier('Table'[col4]) && 'Table'[col5] <= Earlier('Table'[col5])))

    Please help me...if any other solution to cumulative amount without create calculated columns please let me know...

    note: the following below measure gives me the cumulative amount.

    Measure1: Calculate(SUM([Amount]), Filter(All(Calendar), Calendar[Date] <= Max(Calendar[Date]))

    its gives me the result but... I need to calculate returns which depends on the above measure... so while I am using above measure for returns ..its give me very slow performance....

    Monday, March 23, 2015 9:52 AM

Answers

  • Try this instead:

    CALCULATE(
        SUM( Table[Amount] )
        ,ALL( Table )
        ,Table[Date] <= EARLIER( Table[Date] )
        ,Table[col1] <= EARLIER( Table[col1] )
        ,Table[Col2] <= EARLIER( Table[col2] )
        ,Table[col3] <= EARLIER( Table[col3] )
        ,Table[col4] <= EARLIER( Table[col4] )
        ,Table[col5] <= EARLIER( Table[col5] )
    )

    CALCULATE() automatically evaluates all filter arguments in a logical and.

    Tuesday, March 24, 2015 9:56 PM

All replies

  • Try this instead:

    CALCULATE(
        SUM( Table[Amount] )
        ,ALL( Table )
        ,Table[Date] <= EARLIER( Table[Date] )
        ,Table[col1] <= EARLIER( Table[col1] )
        ,Table[Col2] <= EARLIER( Table[col2] )
        ,Table[col3] <= EARLIER( Table[col3] )
        ,Table[col4] <= EARLIER( Table[col4] )
        ,Table[col5] <= EARLIER( Table[col5] )
    )

    CALCULATE() automatically evaluates all filter arguments in a logical and.

    Tuesday, March 24, 2015 9:56 PM
  • Thanks for reply,

    I tried below formula...and its working fine for me.....

    Calculate(
     SUM(Table[Amount])
    ,ALLExcept( Table, Table[Col1], Table[Col2].... )
    ,Table[Date] <= EARLIER( Table[Date] )

    but when I try to process the same table... its taking very long time...

    No of rows: 50,00,000

    can I increase the performance...


    Thursday, March 26, 2015 11:56 AM
  • Does anyone have a suggestion for Manikant to increase perf?

    Thanks!


    Ed Price, Azure & Power BI Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Thursday, April 2, 2015 5:51 PM