Recursion Power BI Problem

[evedang]

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])))

[evedang]

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

[Ashish_Mathur]

Hi,

Share some data, explain the question and show the expected result.