Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Cumulative total based on sequence number

I need some help with a new measure or calculcated colum/table. I'm not sure how to go about doing what I need to do. This is what I have at this point for my data. Column 1 is the sequence number, 2...
Anonymous's avatar
Anonymous
Not applicable
6 years ago

FYI - the QTY delivered is from a different table and is being pulled in with a relationship tied to the Item. 

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

Maybe these two columns:

 

CumulativeQuantityToBuild = SUMX(FILTER('Table (4)',[Sequence] <= EARLIER([Sequence]) && [Item] = EARLIER([Item])),[QuantityToBuild])

Qty Needed For Sequence = 
    VAR __Needed = [CumulativeQuantityToBuild] - [Qty Delivered]
RETURN
    IF(__Needed < 0,0,__Needed)
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    I cant seem to get this to work - potentially because the main table I'm working off of is a calculated table? Not sure if that matters or not.....Maybe I need to add more to my already calculated table? Here is calculation for the current table I'm using

     

    Table 2 =
VAR temp =
    ADDCOLUMNS (
        'Table',
        "MinS",
        VAR s = [Sequence]
        VAR C = [Item]
        RETURN
            MINX (
                FILTER (
                    'Table',
                    VAR c1 = [Item]
                    VAR s1 = [Sequence]
                    RETURN
                        CALCULATE (
                            COUNTROWS ( 'Table' ),
                            FILTER (
                                ALL ( 'Table' ),
                                'Table'[Item] = c1
                                    && 'Table'[Sequence] <= s
                                    && 'Table'[Sequence] >= s1
                            )
                        ) = s - s1 + 1
                ),
                [Sequence]
            )
    )
RETURN
    SELECTCOLUMNS (
        SUMMARIZE (
            temp,
            [MinS],
            "Sequence", RANKX ( temp, [MinS],, ASC, DENSE ),
            "Item", MAX ( 'Table'[Item] ),
            "QuantityToBuild", SUM ( 'Table'[QuantityToBuild] ),
            "Date", MAX ( 'Table'[Date] )
        ),
        "Sequence", [Sequence],
        "Item", [Item],
        "QuantityToBuild", [QuantityToBuild],
        "Date", [Date]
    )

     Do I just need to add to this?