Forum Discussion

bhelou's avatar
bhelou
Icon for Responsive Resident rankResponsive Resident
5 years ago
Solved

Running Total

Dear ,

 

Kindly i am trying to make a running total for the balance as shown in the image , 
it is based on a rank by vendor :

i have tried a quick measure it wont give a rnning total correct nor the total amount correct

1 - Running total measure : 

Balance running total in Rank_By_Vendor 2 =
CALCULATE(
    SUM('Test'[Balance]),
    FILTER(
        ALLSELECTED('Test'[Rank_By_Vendor]),
        ISONORAFTER('Test'[Rank_By_Vendor], MIN('Test'[Rank_By_Vendor]), DESC)
    )
)
 
2 : i have tried another measure :
Balance Running Total =
CALCULATE(
    SUM('Test'[Balance]),
    FILTER(
        (Test),
        Test[Vendor_Number] = LASTNONBLANK(Test[Vendor_Number],Test[Vendor_Number])
    )
)
 
// it gave me the balance correct but no running total .
 
balance in running total should be like this :
-130,725.00
-155,925.00
-173,250.00
etc........
 
 
Many thanks for your help .
 
 
Need Help
  • bhelou's avatar
    bhelou
    5 years ago

    Dear amitchandak 

    Thank you for the measure , i made it as follow and worked 

    Balance 1 =
    CALCULATE(
    SUM('Test'[Balance]),
    FILTER(
    ALLSELECTED(Test),Test[Rank_By_Vendor]<=MAX(Test[Rank_By_Vendor])))

    Many thanks for your help 

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    bhelou , you need to have some unique value for running total.

     

    Try like

     

    CALCULATE(
    SUM('Test'[Balance]),
    FILTER(
    ALLSELECTED('Test'[TransactionLine_Unique]),
    'Test'[TransactionLine_Unique]>= MIN(TransactionLine_Unique))
    )

     

    or


    CALCULATE(
    SUM('Test'[Balance]),
    FILTER(
    ALLSELECTED('Test'[TransactionLine_Unique]),
    'Test'[TransactionLine_Unique]<= MAX(TransactionLine_Unique))
    )

     

    or

     

    CALCULATE(
    SUM('Test'[Balance]),
    FILTER(
    ALLSELECTED('Test'),
    'Test'[TransactionLine_Unique]<= MAX(TransactionLine_Unique))
    )

    • bhelou's avatar
      bhelou
      Icon for Responsive Resident rankResponsive Resident
      5 years ago

      Dear amitchandak 

      Thank you for the measure , i made it as follow and worked 

      Balance 1 =
      CALCULATE(
      SUM('Test'[Balance]),
      FILTER(
      ALLSELECTED(Test),Test[Rank_By_Vendor]<=MAX(Test[Rank_By_Vendor])))

      Many thanks for your help