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
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?