Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Cumulative Total by Group

    Posted Sep 22, 2020 03:10 AM
    Hi,
    I am adding a new column using DAX to calculate a running total for each group. Could somebody help me understand why my formula doesn't work as intended?

    I have copied the formula and the data table from this discussion:


    RunningTotal = CALCULATE( SUM('Input Table'[Value]), FILTER( ALLEXCEPT('Input Table', 'Input Table'[Project]), 'Input Table'[Period]<=MAX('Input Table'[Period]) ) )​


    Thank you,

    ------------------------------
    Saulius G
    ------------------------------


  • 2.  RE: Cumulative Total by Group

    Top Contributor
    Posted Sep 22, 2020 03:18 AM
    Try to create Measure instead of Column

    ------------------------------
    Farhan Ahmed
    Senior Business Intelligence Consultant
    karachi
    3452523688
    ------------------------------



  • 3.  RE: Cumulative Total by Group

    Posted Sep 22, 2020 03:33 AM
    Hi Farhan,

    Thanks for the quick reply. I need this to be at individual row level, as I will then do additional operations with the cumulative total. See the full sample table. I need to evaluate whether Project-Period combination will have enough inventory, considering that it is drawing from total inventory at a Project level.


    Thanks,

    ------------------------------
    Saulius G
    ------------------------------



  • 4.  RE: Cumulative Total by Group

    Top Contributor
    Posted Sep 22, 2020 03:50 AM
    Then use EARLIER instead of MAX in your codeRunningTotal = CALCULATE( SUM('Input Table'[Value]), FILTER( ALLEXCEPT('Input Table', 'Input Table'[Project]), 'Input Table'[Period]<=EARLIER('Input Table'[Period]) ) )​​

    ------------------------------
    Farhan Ahmed
    Senior Business Intelligence Consultant
    karachi
    3452523688
    ------------------------------



  • 5.  RE: Cumulative Total by Group

    Posted Sep 22, 2020 04:07 AM
    Perfect, thank you!

    Is there any reason why I should use 'filter'?
    It seems to give the same result both with and without this function.



    ------------------------------
    Saulius G
    ------------------------------



  • 6.  RE: Cumulative Total by Group

    Top Contributor
    Posted Sep 22, 2020 05:11 AM
    When you use single-column reference to filter the data without using FILTER it converts the code internally to the FILTER context. However, you cannot write a single filter argument referencing two different columns

    Better detail is provided by @Marco Russo under the article " Filter Arguments"

    Ref: https://www.sqlbi.com/articles/filter-arguments-in-calculate/
    ​​

    ------------------------------
    Farhan Ahmed
    Senior Business Intelligence Consultant
    karachi
    3452523688
    ------------------------------



  • 7.  RE: Cumulative Total by Group

    Posted Sep 22, 2020 11:44 AM
    This is great. Thank you very much Farhan. All is working now.

    ------------------------------
    Saulius G
    ------------------------------



  • 8.  RE: Cumulative Total by Group

    Posted Sep 23, 2020 03:56 PM
    Edited by Saulius G Sep 23, 2020 03:58 PM
    Hi again,

    I ran into a "There is not enough memory to complete this operation" error when running this for 28K rows (it did work for 7K rows).
    Is there a way to make my running total formula more efficient by using variables?

    I am pretty sure that above formula is the issue (I have all columns reduced to bare minimum, disabled 'enable load' for all tables that are not required for reporting in query editor, increased Data Cache Management Options to 31MB, etc).

    I was reading below source, which says that "You should be using variables(Var) for storing results in case of complex calculations"
    https://community.powerbi.com/t5/Desktop/There-is-not-enough-memory-to-complete-this-operation/td-p/688511

    Thanks again,


    ------------------------------
    Saulius G
    ------------------------------



  • 9.  RE: Cumulative Total by Group

    Posted Oct 10, 2020 10:50 AM
    In case anybody stumbles on this topic - I tried this as both a measure and a calculated column. Both would run out of memory for 30K+ rows.

    In the end I have moved this to PowerQuery, following advice below.
    Video 1 for running total (simple, not by group):
    https://www.youtube.com/watch?v=uX3_dnb5on0
    Video 2 for grouped running total
    https://www.youtube.com/watch?v=EFQBMJ6JyCQ


    This was my code, but video explains is really well:
    ...
    #"Sorted Rows - Part&Order" = Table.Sort(#"Removed Other Columns1",{{"PART_NO", Order.Descending}, {"ORDER_NO", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows - Part&Order", {"PART_NO"}, {{"Count", each _, type table [ORDER_NO=nullable text, PART_NO=nullable text, Qty_Remaining=nullable number]}}),

    //Function to calculate running totals

    RunFunction = (RunTable as table) as table=>
    let
    #"Added Index" = Table.AddIndexColumn(RunTable, "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each List.Sum(List.Range(#"Added Index"[Qty_Remaining], 0 , [Index])))
    in
    #"Added Custom",

    //Call the function

    RunTotals = Table.TransformColumns( #"Grouped Rows", {"Count", each RunFunction(_)}),
    #"Expanded Count" = Table.ExpandTableColumn(RunTotals, "Count", {"ORDER_NO", "Qty_Remaining", "Custom"}, {"ORDER_NO", "Qty_Remaining", "Custom"}),

    #"Renamed Columns" = Table.RenameColumns(#"Expanded Count",{{"Custom", "RunningTotal"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Qty_Remaining", type number}, {"RunningTotal", type number}}),
    ...

    ------------------------------
    Saulius G
    ------------------------------



  • 10.  RE: Cumulative Total by Group

    Posted Jan 24, 2022 02:04 PM
    Avoid EARLIER on large datasets.  Microsoft warns it's memory use is exponential because it returns an entire dataset for each row processed.  For example, processing 100 rows could result in 10,000 entries in memory.  Processing 38,000 could potentially result in 38K x 38K.

    https://docs.microsoft.com/en-us/dax/earlier-function-dax

    ------------------------------
    James Thomas
    ------------------------------