Rolling sum based on sorted column

[Anonymous]

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.

[Anonymous]

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. 

[Anonymous]

[Anonymous]

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

[v-zhangti]

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]

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=11317... 

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

[v-zhangti]

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.

[Anonymous]

Hi @v-zhangti,

Please find the updated excel here with both the Transaction table (has Item Count) and Performance table (has VenueCity) joined on Performance Key (Many to One)
https://docs.google.com/spreadsheets/d/1nDnBezke6IbbfvY_GxcSeDcHhSaLEmN9/edit?usp=sharing&ouid=11317...

Thanks

[v-zhangti]

Hi, @Anonymous 

Thank you for your reply. I have understood the relationship between several fields. I simulated some data to reproduce your problem.

Rolling sum refers directly to the source data during calculation.

Tickets Quantity = 
CALCULATE (
    SUM ( 'Transaction'[Item Count] ),
    FILTER (
        ALL( 'Performance' ),
        [VenueCity] = MAX ( Performance[VenueCity] )
    )
)

Rolling Sum =
CALCULATE (
    SUM ( 'Transaction'[Item Count] ),
    FILTER ( ALL ( 'Performance' ), [VenueCity] <= MAX ( Performance[VenueCity] ) )
)

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]

Hi @v-zhangti 

There is a small problem with the rolling sum logic.
Hers is the screenshot without Rolling Sum which shows correct Ticket Qty for only those VenueCity's which have Item Count for it.

Here is the screenshot with Rolling Sum (sorted descending by Ticket Qty) which does not seem correct.

Here is the screenshot again with Rolling Sum, sorted in Ascending Order

Here, I can see Rolling Sum is adding ticket count but in ascending order of VenueCity and it displays all the VenueCity's.

The reason you did not face such an issue is that I had already sent you filtered data. I am again attaching the link with updated data without filters.

https://docs.google.com/spreadsheets/d/1GLQ7TX96b68JoDiDNwZK8We059YaoVrI/edit?usp=sharing&ouid=11317... 
Can you please help me resolve it so that Rolling Sum should display values when sorted by Ticket Qty and should display only when there is Item Count and not for all VenueCity's?

[mattww]

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.

[mattww]

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

[Anonymous]

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?