Forum Discussion

BigKev's avatar
BigKev
Icon for Helper III rankHelper 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 Ru...
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.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper 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
    Icon for Helper III rankHelper III
    5 years ago

    Hi Amit,

     

    I need the solution for calculated column and not measure

     

    Cheers,

    Kev

    • amitchandak's avatar
      amitchandak
      Icon for Super User rankSuper 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])