Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Rolling sum based on sorted column

Hello, 

I have a requirement to visualize cumulative quantities based on sorted calculated measure "Ticket Qty".
I need to show a chart where on Y-axis, I will have the Ticket Qty and on the X-axis, I will have VenueCity and on Y2-axis, the cumulative total amount calculated based on the sorted "Ticket Qty" column (descending). Please see below. 

Can you please help to create a Rolling Sum of Ticket Qty?
Thanks a lot.
Need Help

8 Replies

  • mattww's avatar
    mattww
    Responsive Resident
    4 years ago

    Hi Anonymous 

     

    Try adding a Calculated Column to calculate your sort order

     

    QtyRankCol = RANKX(EventTable,EventTable[Ticket Qty])
     
    Then use a Measure for your running total
     
    RunningTotal = 
    CALCULATE(
        [TicketQtyMeasure],
        FILTER(
            ALL('EventTable'),
            ISONORAFTER('EventTable'[QtyRankCol], MAX('EventTable'[QtyRankCol]), DESC)
        )
    )
     
    If you have a lot of interaction going on that would affect the rank, this may cause you some issues, but otherwise it works for me with the example you gave
     

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi mattww,

      I am not able to get the correct results for QtyRankCol. My data model has the below-shown tables (Transaction table which has Item Count and is connected with Performance table which I earlier mentioned as Event table and this table has VenueCity).
      So how I am calculating Ticket Qty is the sum of Item Count for Item Type = Ticket.

       

      I am not sure as to how to get QtyRankCol correctly, can you please help me work on my scenario?

  • v-zhangti's avatar
    v-zhangti
    Community Support
    4 years ago

    Hi, Anonymous 

     

    I tested it. It is best to use the calculation column for rolling summation. If the Ticket Qty is measure, the Ticket Qty cannot be referenced.

    If Ticket Qty is a calculated column.

     

    Rank = 
RANKX ( 'Table', 'Table'[Ticket Qty], [Ticket Qty], DESC )

    Rolling Sum = 
CALCULATE (
    SUM ( 'Table'[Ticket Qty] ),
    FILTER ( ALL ( 'Table' ), [Rank] <= EARLIER ( 'Table'[Rank] ) )
)

     

     

     

    Rolling Sum Measure = 
CALCULATE (
    SUM ( 'Table'[Ticket Qty] ),
    FILTER ( ALL ( 'Table' ), [Rank] <= MAX ( 'Table'[Rank] ) )
)

     

    If my method can't solve your problem. If Ticket Qty can only be measure, can you provide the source data of Ticket Qty and I'll test it again? Thank you.

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi v-zhangti,
      You are right Tickets Quantity cannot be referenced if it is a calculated measure and I tried the same logic in a calculated column but it gives me wrong results.
      DAX:

      Tickets Quantity =
      Calculate(SUM('Transaction'[Item Count]),FILTER(ALLSELECTED('Item Type'),[Item Type]="Ticket"))

       

      Here is the link to the Ticket Qty sample source data (excel) file.
      https://docs.google.com/spreadsheets/d/1eIA2Abarbkl4nXhvtA7gz3hIExB5j13o/edit?usp=sharing&ouid=113177030674399990149&rtpof=true&sd=true 

      Please let me know if you need any more fields data or information.

      • v-zhangti's avatar
        v-zhangti
        Community Support
        4 years ago

        Hi, Anonymous 

         

        Thank you for your reply.

        You also need to tell me how you got the table below：

        VenueCity Item Type Tickets Quantity
        Boston Ticket 442393
        West Palm Beach Ticket 80257
        Raleigh Ticket 28927
        National Harbor Ticket 21212
        New York Ticket 546

         

        Because the field "ventuecity" does not appear in the data you provided.

        Look forward to your reply.

         

        Best Regards,

        Community Support Team _Charlotte

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.