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 ...
Need Help
v-zhangti's avatar
v-zhangti
Icon for Community Support rankCommunity 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
      Icon for Community Support rankCommunity 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.