Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
I am trying to come up with a DAX measure that works like a for/while loop. The calculation process is shown below. Basically this rollup calculation will stop when Previous Rollup = Current Rollup. If it is impractical to use this condition, I think about a for loop with about 10 iterations. I did research on the forum for for/while loop problems, but none of them refers back to previous iteration. 😢 Please help 😢
Rollup1 = [UpdatedEOM]-CALCULATE(SUM('Sample2'[UpdatedEOM]), FILTER(ALL('Sample2'),[Horizontal Facility]=MAX(Sample2[Facility])))
Rollup2 = [UpdatedEOM]-CALCULATE(SUM([Rollup1]), FILTER(ALL('Sample2'),[Horizontal Facility]=MAX(Sample2[Facility])))
Rollup3 = [UpdatedEOM]-CALCULATE(SUM([Rollup2]), FILTER(ALL('Sample2'),[Horizontal Facility]=MAX(Sample2[Facility])))
Rollup4 = [UpdatedEOM]-CALCULATE(SUM([Rollup3]), FILTER(ALL('Sample2'),[Horizontal Facility]=MAX(Sample2[Facility])))
Rollup5 = [UpdatedEOM]-CALCULATE(SUM([Rollup4]), FILTER(ALL('Sample2'),[Horizontal Facility]=MAX(Sample2[Facility])))
Thank @Ashish_Mathur for your response. For further explaination, I have the table below, with Facility, UpdatedEOM and Horizontal Facility are raw columns. Rollup 1-7 are calculated columns. For each Facility, its rollup = UpdatedEOM - Sum of rollup from other facilities that have it as "Horizontal Facility". For example, Rollup2 of AL = UpdatedEOM - SUM(Rollup1 of COT and DEV, which have AL as Horizontal Facility). The problem is these rollup depend on each other and I have to calculate multiple times until the all the current rollups = all the previous rollups (For example all Rollup6 = all Rollup7).
The first three rollups are calculated below:
Rollup1 = [UpdatedEOM]-CALCULATE(SUM('Sample2'[UpdatedEOM]), FILTER(ALL('Sample2'),[Horizontal Facility]=MAX(Sample2[Facility])))
Rollup2 = [UpdatedEOM]-CALCULATE(SUM([Rollup1]), FILTER(ALL('Sample2'),[Horizontal Facility]=MAX(Sample2[Facility])))
Rollup3 = [UpdatedEOM]-CALCULATE(SUM([Rollup2]), FILTER(ALL('Sample2'),[Horizontal Facility]=MAX(Sample2[Facility])))
.....
Facility | UpdatedEOM | Horizontal Facility | Rollup1 | Rollup2 | Rollup3 | Rollup4 | Rollup5 | Rollup6 | Rollup7 |
AL | 336,494 | EO | 1652222 | 2,074,214 | 2,074,214 | 2,074,214 | 2,074,214 | 2,074,214 | 2,074,214 |
CAN | 616,878 | PER | -359749 | 346,564 | -89,914 | -89,914 | -89,914 | -89,914 | -89,914 |
KIN | 1,159 | UCGS | 1159 | 1,159 | 1,159 | 1,159 | 1,159 | 1,159 | 1,159 |
DES | -11,511 | CALW | -11511 | -11,511 | -11,511 | -11,511 | -11,511 | -11,511 | -11,511 |
GLO | 421,992 | COT | 421992 | 421,992 | 421,992 | 421,992 | 421,992 | 421,992 | 421,992 |
HER | 956,675 | CALW | 956675 | 956,675 | 956,675 | 956,675 | 956,675 | 956,675 | 956,675 |
LUJ | 4,200 | USE | 4200 | 4,200 | 4,200 | 4,200 | 4,200 | 4,200 | 4,200 |
MIR | 0 |
| 0 | 0 | 0 | 0 | 0 | 0 | 0 |
MON | 251,213 | EO | 251213 | 251,213 | 251,213 | 251,213 | 251,213 | 251,213 | 251,213 |
PEC | 889,727 | CALW | -256975 | 415,525 | -561,102 | 145,211 | -291,267 | -291,267 | -291,267 |
HORN | 55,622 | PER | 55622 | 55,622 | 55,622 | 55,622 | 55,622 | 55,622 | 55,622 |
SOUTH | 705,084 | UCLS | 268605 | 268,605 | 268,605 | 268,605 | 268,605 | 268,605 | 268,605 |
ST54 | 71,291 | EO | 71291 | 71,291 | 71,291 | 71,291 | 71,291 | 71,291 | 71,291 |
ST19 | 179,182 | EO | 179182 | 179,182 | 179,182 | 179,182 | 179,182 | 179,182 | 179,182 |
UCR | 70 | UCLS | 70 | 70 | 70 | 70 | 70 | 70 | 70 |
VEL | 21,543 | EO | 21543 | 21,543 | 21,543 | 21,543 | 21,543 | 21,543 | 21,543 |
POWDER | 54,809 | CALE | 54809 | 54,809 | 54,809 | 54,809 | 54,809 | 54,809 | 54,809 |
PL2 | 592,779 | MAN | -272078 | -272,078 | -272,078 | -272,078 | -272,078 | -272,078 | -272,078 |
PL1 | 0 | GLAKE | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
TOWNSEN | 14,018 | CALW | 14018 | 14,018 | 14,018 | 14,018 | 14,018 | 14,018 | 14,018 |
LUJPEC | 144 | CALW | 144 | 144 | 144 | 144 | 144 | 144 | 144 |
PIT | 361,110 | CALW | 270382 | 274,582 | 274,582 | 274,582 | 274,582 | 274,582 | 274,582 |
JANE | 306,037 | CALW | -4347 | 50,463 | 50,463 | 50,463 | 50,463 | 50,463 | 50,463 |
SALES | 996,978 | CALW | 1951253 | 3,403,755 | 3,854,625 | 4,276,617 | 4,276,617 | 4,276,617 | 4,276,617 |
BRIDGE | 190,751 | CALW | 190751 | 190,751 | 190,751 | 190,751 | 190,751 | 190,751 | 190,751 |
ALCHE | 122,580 | JANE | 122580 | 122,580 | 122,580 | 122,580 | 122,580 | 122,580 | 122,580 |
CALE | 187,803 | JANE | 132993 | 132,993 | 132,993 | 132,993 | 132,993 | 132,993 | 132,993 |
CALW | 5,460,471 |
| 1756544 | 2,350,081 | 166,070 | 691,827 | -436,479 | 0 | 0 |
KI | 0 |
| 0 | 0 | 0 | 0 | 0 | 0 | 0 |
COT | -453,015 | AL | -875007 | -875,007 | -875,007 | -875,007 | -875,007 | -875,007 | -875,007 |
DEV | -862,713 | AL | -862713 | -862,713 | -862,713 | -862,713 | -862,713 | -862,713 | -862,713 |
EO | -2,834,391 | SALES | -3694114 | -5,009,842 | -5,431,834 | -5,431,834 | -5,431,834 | -5,431,834 | -5,431,834 |
GLAKE | 519,539 | PL2 | 519539 | 519,539 | 519,539 | 519,539 | 519,539 | 519,539 | 519,539 |
UCGS | 224,306 | CAN | 223147 | 223,147 | 223,147 | 223,147 | 223,147 | 223,147 | 223,147 |
MAN | 1,880,117 | SALES | 1287338 | 2,152,195 | 2,152,195 | 2,152,195 | 2,152,195 | 2,152,195 | 2,152,195 |
CHEVP | 436,479 | SOUTH | 436479 | 436,479 | 436,479 | 436,479 | 436,479 | 436,479 | 436,479 |
PER | 466,774 | PEC | -205727 | 770,901 | 64,587 | 501,066 | 501,066 | 501,066 | 501,066 |
UCLS | 752,321 | CAN | 47167 | 483,646 | 483,646 | 483,646 | 483,646 | 483,646 | 483,646 |
UCRIP | 345,318 | PL2 | 345318 | 345,318 | 345,318 | 345,318 | 345,318 | 345,318 | 345,318 |
UCLEG | 679,928 | PEC | 679928 | 679,928 | 679,928 | 679,928 | 679,928 | 679,928 | 679,928 |
USE | 90,727 | PIT | 86528 | 86,528 | 86,528 | 86,528 | 86,528 | 86,528 | 86,528 |
The expected result is final rollup column having values as Rollup 7 above. I would really appreciate any advice or idea.
Facility | UpdatedEOM | Horizontal Facility | Rollup |
AL | 336,494 | EO | 2,074,214 |
CAN | 616,878 | PER | -89,914 |
KIN | 1,159 | UCGS | 1,159 |
DES | -11,511 | CALW | -11,511 |
GLO | 421,992 | COT | 421,992 |
HER | 956,675 | CALW | 956,675 |
LUJ | 4,200 | USE | 4,200 |
MIR | 0 |
| 0 |
MON | 251,213 | EO | 251,213 |
PEC | 889,727 | CALW | -291,267 |
HORN | 55,622 | PER | 55,622 |
SOUTH | 705,084 | UCLS | 268,605 |
ST54 | 71,291 | EO | 71,291 |
ST19 | 179,182 | EO | 179,182 |
UCR | 70 | UCLS | 70 |
VEL | 21,543 | EO | 21,543 |
POWDER | 54,809 | CALE | 54,809 |
PL2 | 592,779 | MAN | -272,078 |
PL1 | 0 | GLAKE | 0 |
TOWNSEN | 14,018 | CALW | 14,018 |
LUJPEC | 144 | CALW | 144 |
PIT | 361,110 | CALW | 274,582 |
JANE | 306,037 | CALW | 50,463 |
SALES | 996,978 | CALW | 4,276,617 |
BRIDGE | 190,751 | CALW | 190,751 |
ALCHE | 122,580 | JANE | 122,580 |
CALE | 187,803 | JANE | 132,993 |
CALW | 5,460,471 |
| 0 |
KI | 0 |
| 0 |
COT | -453,015 | AL | -875,007 |
DEV | -862,713 | AL | -862,713 |
EO | -2,834,391 | SALES | -5,431,834 |
GLAKE | 519,539 | PL2 | 519,539 |
UCGS | 224,306 | CAN | 223,147 |
MAN | 1,880,117 | SALES | 2,152,195 |
CHEVP | 436,479 | SOUTH | 436,479 |
PER | 466,774 | PEC | 501,066 |
UCLS | 752,321 | CAN | 483,646 |
UCRIP | 345,318 | PL2 | 345,318 |
UCLEG | 679,928 | PEC | 679,928 |
USE | 90,727 | PIT | 86,528 |
Hi,
Share some data, explain the question and show the expected result.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
64 | |
60 | |
51 | |
36 | |
36 |
User | Count |
---|---|
81 | |
72 | |
58 | |
45 | |
44 |