Forum Discussion

BigKev's avatar
BigKev
Helper III
5 years ago
Solved

Running Total for specific categories

Hi All,

 

I have specific needs to create a calculated Column for a running total based on Type and department.

 

Below is my sample data, and what I want to create is a "Calculated Column" on Running total.

DateTypeDepartmentValueRunning Total
1/01/2021ActualSales11
1/01/2021ActualHR22
1/01/2021ActualOperation33
1/01/2021ActualPurchasing44
1/01/2021BudgetSales55
1/01/2021BudgetHR66
1/01/2021BudgetOperation77
1/01/2021BudgetPurchasing88
1/02/2021ActualSales910
1/02/2021ActualHR1012
1/02/2021ActualOperation1114
1/02/2021ActualPurchasing1216
1/02/2021BudgetSales1318
1/02/2021BudgetHR1420
1/02/2021BudgetOperation1522
1/02/2021BudgetPurchasing1624
1/03/2021ActualSales1727
1/03/2021ActualHR1830
1/03/2021ActualOperation1933
1/03/2021ActualPurchasing2036
1/03/2021BudgetSales2139
1/03/2021BudgetHR2242
1/03/2021BudgetOperation2345
1/03/2021BudgetPurchasing2448
1/04/2021ActualSales2552
1/04/2021ActualHR2656
1/04/2021ActualOperation2760
1/04/2021ActualPurchasing2864
1/04/2021BudgetSales2968
1/04/2021BudgetHR3072
1/04/2021BudgetOperation3176
1/04/2021BudgetPurchasing3280

 

As an example, this is how the Actual - Sales Running Total is calculated:

DateTypeDepartmentValueRunning Total
1/01/2021ActualSales11
1/02/2021ActualSales910
1/03/2021ActualSales1727
1/04/2021ActualSales2552

 

As an example, this is how the Budget - Sales Running Total is calculated:

DateTypeDepartmentValueRunning Total
1/01/2021BudgetSales55
1/02/2021BudgetSales1318
1/03/2021BudgetSales2139
1/04/2021BudgetSales2968

 

 

Can anyone please help?

 

TIA.

 

Cheers,

Kev

Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    5 years ago

    Hi, BigKev 

     

    Try to create a column like this:

    Column =
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Department] = EARLIER ( 'Table'[Department] )
            && 'Table'[Type] = EARLIER ( 'Table'[Type] )
            && 'Table'[Date] <= EARLIER ( 'Table'[Date] )
    )
)

    result:

    Please refer to the attachment below for details

     

     

    Hope this helps.

     

    Best Regards,
    Community Support Team _ Zeon Zheng
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    This calculated column formula works

    =CALCULATE(SUM(Data[Value]),FILTER(Data,Data[Type]=EARLIER(Data[Type])&&Data[Department]=EARLIER(Data[Department])&&Data[Date]<=EARLIER(Data[Date])))

    Hope this helps.

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    BigKev , Try a meaure like

     

    Sumx(filter(allselected(Table), table[Type] = max(Table[Type]) && Table[Date] = max(Table[Date])), Table[Value])

    • BigKev's avatar
      BigKev
      Helper III
      5 years ago

      Hi Amit,

       

      I need the solution for calculated column and not measure

       

      Cheers,

      Kev

      • amitchandak's avatar
        amitchandak
        Super User
        5 years ago

        BigKev , replace Max -> earlier and remove allselected and try in column

         

        This trick work, most of the time

         

        Sumx(filter((Table), table[Type] = earlier (Table[Type]) && Table[Date] = earlier (Table[Date])), Table[Value])

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    5 years ago

    Hi, BigKev 

     

    Try to create a column like this:

    Column =
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Department] = EARLIER ( 'Table'[Department] )
            && 'Table'[Type] = EARLIER ( 'Table'[Type] )
            && 'Table'[Date] <= EARLIER ( 'Table'[Date] )
    )
)

    result:

    Please refer to the attachment below for details

     

     

    Hope this helps.

     

    Best Regards,
    Community Support Team _ Zeon Zheng
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

    This calculated column formula works

    =CALCULATE(SUM(Data[Value]),FILTER(Data,Data[Type]=EARLIER(Data[Type])&&Data[Department]=EARLIER(Data[Department])&&Data[Date]<=EARLIER(Data[Date])))

    Hope this helps.