Calculating SUM of dynamic amount of rows from second table

[Anonymous]

What I have is data like this:

Category	Forecast on Cycle Time	Order	Title
Category A	2	1	XYZ
Category A	2	2	XYZ
Category B	3	3	XYZ
Category A	5	4	XYZ
Category C	1	5	XYZ
Category A	5	6	XYZ
Category C	10	7	XYZ
Category B	10	8	XYZ

Now, what my data is trying to do is to try and forecast the delivery (based on the forecast) when certain items will be complete AND when certain category of things are complete - to help inform decision making about prioritizing the work (changing the order).

What I'm trying to achieve here is this - I want to be able to pick a category (let's say Category A) and calculate the forecasted delivery based on the forecast but it NEEDS to take into account every other item from different category that is prioritized somewhere "in the middle". To do that - instead of a simple SUM function for each Forecast for Category A - I need to do something different here, which includes:

• identifying the order position which includes the furthest Category A item on the ordered list (in this case - Order #6)
• calculate SUM of the forecast for every item on the list (regardless of the Category) from first to the MAX identified in previous step - so basically do a SUM of X rows in that unfiltered table, where the X is defined by the maximum

How can I achieve that?

I've tried bunch of stuff - I've already managed to get my max (by a simple calculation of MAX of that column) but so far I was unable to use that dynamic MAX to limit the number of rows taken into calculation - anything I did either resulted in (a) filtered table which summarized only the items that belonged to Category A or (b) didn't care about the maximum and calculated the SUM for the whole table. I don't seem to get a right way to use dynamic/filtered MAX and apply it to unfiltered results somehow.

[Greg_Deckler]

@Anonymous I believe you want:

Measure =
  VAR __Category = MAX('Table'[Category])
  VAR __Max = MAXX(FILTER(ALL('Table'),[Category] = __Category),[Forecast on Cycle Time])
RETURN
  SUMX(FILTER(ALL('Table'),[Forecast on Cycle Time] <= __Max),[Forecast on Cycle Time])

[Anonymous · Accepted solution]

However! I've found the issue.

It shouldn't be, as You've suggested:

---

Measure =
  VAR __Category = MAX('Table'[Category])
  VAR __Max = MAXX(FILTER(ALL('Table'),[Category] = __Category),[Forecast on Cycle Time])
RETURN
  SUMX(FILTER(ALL('Table'),[Forecast on Cycle Time] <= __Max),[Forecast on Cycle Time])

---

But rather:

  VAR __Category = MAX('Test'[Category])
  VAR __Max = MAXX(FILTER(ALL('Test'),[Category]= __Category),[Order])
RETURN
  SUMX(FILTER(ALL('Test'),[Order] <= __Max),[Forecast])

In the VAR __Max we are looking for the maximum ORDER not the maximume value of forecast 🙂

And that suits the criteria and provide correct results.

[Anonymous]

I think You're onto something here. I hoped it will work but it seems to provide invalid data for some reason.Or maybe I have made some simple mistake. I wanted to upload the files to show You what's wrong but it doesn't let me for some reason. Anyway, below are the screenshots:

1. When none of the category is chosen:

2. When I pick one of the category to filter:

And here are the values that I've measured in Excel to help me verify if these measures are working correctly:

Category	Title	Story Points	Order				MAX	SUM of ALL (based on MAX)
Feat 1	XYZ	2	1			Feat 1	5	25
Feat 2	XYZ	5	2			Feat 2	9	47
Feat 3	XYZ	3	3			Feat 3	10	57
Feat 1	XYZ	10	4			Feat 4	9	34
Feat 1	XYZ	5	5
Feat 2	XYZ	1	6
Feat 3	XYZ	3	7
Feat 4	XYZ	5	8
Feat 2	XYZ	13	9
Feat 3	XYZ	10	10
	SUM:	57

So for Feat 4 that I've picked up on the screenshot, the value should be 34 and it is 24.