Forum Discussion

ashaikh's avatar
ashaikh
Icon for Helper III rankHelper III
9 years ago
Solved

running total

Hello All,

 

I have one scenario which I am trying to do

 

I hava table having ticket data.

 

Ticket No | Data Create | Day| Ticket allotted per day
1         |2017-08-01   | 1     | 26
2         |2017-08-01   | 1     | 26
3         |2017-08-02   | 2     | 26
4         |2017-08-03   | 3     | 26
5         |2017-08-03   | 3     | 26

Now the solution I want to see as follows:

Day | Actual Ticket Per Day |  Actual Running Total | Allotted Running Total
1   | 2                     | 2                     | 26
2   | 1                     | 3                     | 52
3   | 2                     | 5                     | 78

Now I can calculate Actual Running Total, but I am facing issues calculating Allotted Running Total.

 

Any help with calculating Allotted Running Total is appreciated

 

Need Help
Tips and Tricks

7 Replies

  • AlbertoFerrari's avatar
    AlbertoFerrari
    Icon for Most Valuable Professional rankMost Valuable Professional
    9 years ago

    Is the value of Ticket allotted per day the same for all the rows in the same day? If so, you can do as follows:

    Allotted Running Total :=
VAR CurrentDay = MAX ( Table[Day] )
CALCULATE ( 
    SUMX ( 
        SUMMARIZE ( 
            Table,
            Table[Day],
            Table[Ticket allotted per day]
        ),
        Table[Ticket allotted per day]
    ),
    Table[Day] <= CurrentDay
)

    If, on the other hand, the value might be different for the same day, then this code will result in a wrong result (but, at this point, you have a modeling issue).

    That said, it would be better to store the "ticket alloted per day" in a table containing one row per day, in that case the solution would be a much better one.

    • ashaikh's avatar
      ashaikh
      Icon for Helper III rankHelper III
      9 years ago

      I have a table with ticket allocated per day month wise.

       

      This number comes from a monthly allocated ticket / number of days in month. So I created a monthwise table having ticket allocated per day

    • ashaikh's avatar
      ashaikh
      Icon for Helper III rankHelper III
      9 years ago

      I am not getting the desired result.

       

      What  am I getting is same value for throught out the days. i.e. 26

      • AlbertoFerrari's avatar
        AlbertoFerrari
        Icon for Most Valuable Professional rankMost Valuable Professional
        9 years ago

        Well, prepare a PBIX file with some demo data, and I will send you the right code. Writing blind is always challenging, I probably missed something.


        Have fun with DAX!

        Alberto Ferrari
        http://www.sqlbi.com