Forum Discussion
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...
Greg_Deckler
Community Champion
Community ChampionMaybe 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)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?