Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.
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:
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.
Solved! Go to 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 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])
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.
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.
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Check out the October 2025 Power BI update to learn about new features.