Forum Discussion

chaitanya1's avatar
chaitanya1
Icon for Helper I rankHelper I
1 year ago
Solved

Custom Column for Cummulative TR in power query

let // Load the Serviceable Feeder table ServiceableFeeders = ServiceableFeeder, // Load the Master Address List (2) table with MonthsSinceGoLive MonthsTable = QuartersSinceLiveSeries, // Add a ...
data protection
Interesting Links
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi chaitanya1 ,

     

    Please try like:

    Cumulative TR = 
VAR __curr_index = 'Table'[Index]
VAR __result = CALCULATE(SUM('Table'[TR]), FILTER(ALL('Table'), 'Table'[Index] <= __curr_index))
RETURN
    __result

    or:

    Cumulative TR = 
VAR __curr_index = 'Table'[Index]
VAR __result = CALCULATE(SUM('Table'[TR]),  'Table'[Index] <= __curr_index, ALLEXCEPT('Table','Table'[Index]))
RETURN
    __result

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

chaitanya1's avatar
chaitanya1
Icon for Helper I rankHelper I
1 year ago

bhanu_gautam i have 2.5 million rows in table it is taking hours by using this List.Sum(List.FirstN) reading and loading every row there is any alternative to that please i have try same method before.

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    1 year ago

    Hi bhanu_gautam 

     

    Why create this using M? While it is feasible, generating a cumulative value column in a table with 2.5 million rows will be computationally expensive and slow if it doesn't time out altogether. I've attempted this with rows generated entirely within the query editor, and that one even timed out. Handling external data with additional ETL processes applied would only make it even worse. It is better off handled as a DAX measure or be pushed to the source.

    • chaitanya1's avatar
      chaitanya1
      Icon for Helper I rankHelper I
      1 year ago

      bhanu_gautam can you help me with that DAX i am not getting cumulative view through DAX.

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi chaitanya1 ,

         

        Please try like:

        Cumulative TR = 
VAR __curr_index = 'Table'[Index]
VAR __result = CALCULATE(SUM('Table'[TR]), FILTER(ALL('Table'), 'Table'[Index] <= __curr_index))
RETURN
    __result

        or:

        Cumulative TR = 
VAR __curr_index = 'Table'[Index]
VAR __result = CALCULATE(SUM('Table'[TR]),  'Table'[Index] <= __curr_index, ALLEXCEPT('Table','Table'[Index]))
RETURN
    __result

        Best Regards,
        Gao
        Community Support Team

         

        If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
        If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

        How to get your questions answered quickly --  How to provide sample data in the Power BI Forum