Creating Sub Totals for a Sort, Unique, Filter, Choose Cols dynamically

[Peter Davison]

I have created the formula below using the LET function covering the columns B to Z in my active sheet (Report Writer) extracting data from the source sheet (Data Analysis).
However, I feel there is probably a better productive way to achieve the result.
Any help as always is very well appreciated.
This is my formula.

=IFERROR(
LET(d,SORT(SORT(UNIQUE(FILTER(CHOOSECOLS('Data Analysis'!$B$12:$HZ$1000,FILTER(XMATCH($B$11:$Z$11,'Data Analysis'!$B$11:$HZ$11),$B$11:$Z$11<>"")),IF($E$8="",'Data Analysis'!$G$12:$G$1000<>"",('Data Analysis'!$G$12:$G$1000<>"")*('Data Analysis'!$L$12:$L$1000=$E$8)))),2),IFERROR(MATCH("Y",$D$6:$Z$6,0),1)),u,UNIQUE(INDEX(d,,2)),SORTBY(VSTACK(d,
CHOOSE(SEQUENCE(,25),
u,
"",
"",
"",
IF(XLOOKUP(F$11,'Global Rules'!AJ12:AJ1000,'Global Rules'!AI12:AI1000)="Text","",SUMIFS(INDEX('Data Analysis'!$B:$HZ,,MATCH(F$11,'Data Analysis'!$B$11:$HZ$11,0)),'Data Analysis'!L:L,u)),
IF(XLOOKUP(G$11,'Global Rules'!AJ12:AJ1000,'Global Rules'!AI12:AI1000)="Text","",SUMIFS(INDEX('Data Analysis'!$B:$HZ,,MATCH(G$11,'Data Analysis'!$B$11:$HZ$11,0)),'Data Analysis'!L:L,u)),
IF(XLOOKUP(H$11,'Global Rules'!AJ12:AJ1000,'Global Rules'!AI12:AI1000)="Text","",SUMIFS(INDEX('Data Analysis'!$B:$HZ,,MATCH(H$11,'Data Analysis'!$B$11:$HZ$11,0)),'Data Analysis'!L:L,u)),
IF(XLOOKUP(I$11,'Global Rules'!AJ12:AJ1000,'Global Rules'!AI12:AI1000)="Text","",SUMIFS(INDEX('Data Analysis'!$B:$HZ,,MATCH(I$11,'Data Analysis'!$B$11:$HZ$11,0)),'Data Analysis'!L:L,u)),
IF(XLOOKUP(J$11,'Global Rules'!AJ12:AJ1000,'Global Rules'!AI12:AI1000)="Text","",SUMIFS(INDEX('Data Analysis'!$B:$HZ,,MATCH(J$11,'Data Analysis'!$B$11:$HZ$11,0)),'Data Analysis'!L:L,u)),
IF(XLOOKUP(K$11,'Global Rules'!AJ12:AJ1000,'Global Rules'!AI12:AI1000)="Text","",SUMIFS(INDEX('Data Analysis'!$B:$HZ,,MATCH(K$11,'Data Analysis'!$B$11:$HZ$11,0)),'Data Analysis'!L:L,u)),
IF(XLOOKUP(L$11,'Global Rules'!AJ12:AJ1000,'Global Rules'!AI12:AI1000)="Text","",SUMIFS(INDEX('Data Analysis'!$B:$HZ,,MATCH(L$11,'Data Analysis'!$B$11:$HZ$11,0)),'Data Analysis'!L:L,u)),
IF(XLOOKUP(M$11,'Global Rules'!AJ12:AJ1000,'Global Rules'!AI12:AI1000)="Text","",SUMIFS(INDEX('Data Analysis'!$B:$HZ,,MATCH(M$11,'Data Analysis'!$B$11:$HZ$11,0)),'Data Analysis'!L:L,u)),
IF(XLOOKUP(N$11,'Global Rules'!AJ12:AJ1000,'Global Rules'!AI12:AI1000)="Text","",SUMIFS(INDEX('Data Analysis'!$B:$HZ,,MATCH(N$11,'Data Analysis'!$B$11:$HZ$11,0)),'Data Analysis'!L:L,u)),
IF(XLOOKUP(O$11,'Global Rules'!AJ12:AJ1000,'Global Rules'!AI12:AI1000)="Text","",SUMIFS(INDEX('Data Analysis'!$B:$HZ,,MATCH(O$11,'Data Analysis'!$B$11:$HZ$11,0)),'Data Analysis'!L:L,u)),
IF(XLOOKUP(P$11,'Global Rules'!AJ12:AJ1000,'Global Rules'!AI12:AI1000)="Text","",SUMIFS(INDEX('Data Analysis'!$B:$HZ,,MATCH(P$11,'Data Analysis'!$B$11:$HZ$11,0)),'Data Analysis'!L:L,u)),
IF(XLOOKUP(Q$11,'Global Rules'!AJ12:AJ1000,'Global Rules'!AI12:AI1000)="Text","",SUMIFS(INDEX('Data Analysis'!$B:$HZ,,MATCH(Q$11,'Data Analysis'!$B$11:$HZ$11,0)),'Data Analysis'!L:L,u)),
IF(XLOOKUP(R$11,'Global Rules'!AJ12:AJ1000,'Global Rules'!AI12:AI1000)="Text","",SUMIFS(INDEX('Data Analysis'!$B:$HZ,,MATCH(R$11,'Data Analysis'!$B$11:$HZ$11,0)),'Data Analysis'!L:L,u)),
IF(XLOOKUP(S$11,'Global Rules'!AJ12:AJ1000,'Global Rules'!AI12:AI1000)="Text","",SUMIFS(INDEX('Data Analysis'!$B:$HZ,,MATCH(S$11,'Data Analysis'!$B$11:$HZ$11,0)),'Data Analysis'!L:L,u)),
IF(XLOOKUP(T$11,'Global Rules'!AJ12:AJ1000,'Global Rules'!AI12:AI1000)="Text","",SUMIFS(INDEX('Data Analysis'!$B:$HZ,,MATCH(T$11,'Data Analysis'!$B$11:$HZ$11,0)),'Data Analysis'!L:L,u)),
IF(XLOOKUP(U$11,'Global Rules'!AJ12:AJ1000,'Global Rules'!AI12:AI1000)="Text","",SUMIFS(INDEX('Data Analysis'!$B:$HZ,,MATCH(U$11,'Data Analysis'!$B$11:$HZ$11,0)),'Data Analysis'!L:L,u)),
IF(XLOOKUP(V$11,'Global Rules'!AJ12:AJ1000,'Global Rules'!AI12:AI1000)="Text","",SUMIFS(INDEX('Data Analysis'!$B:$HZ,,MATCH(V$11,'Data Analysis'!$B$11:$HZ$11,0)),'Data Analysis'!L:L,u)),
IF(XLOOKUP(W$11,'Global Rules'!AJ12:AJ1000,'Global Rules'!AI12:AI1000)="Text","",SUMIFS(INDEX('Data Analysis'!$B:$HZ,,MATCH(W$11,'Data Analysis'!$B$11:$HZ$11,0)),'Data Analysis'!L:L,u)),
IF(XLOOKUP(X$11,'Global Rules'!AJ12:AJ1000,'Global Rules'!AI12:AI1000)="Text","",SUMIFS(INDEX('Data Analysis'!$B:$HZ,,MATCH(X$11,'Data Analysis'!$B$11:$HZ$11,0)),'Data Analysis'!L:L,u)),
IF(XLOOKUP(Y$11,'Global Rules'!AJ12:AJ1000,'Global Rules'!AI12:AI1000)="Text","",SUMIFS(INDEX('Data Analysis'!$B:$HZ,,MATCH(Y$11,'Data Analysis'!$B$11:$HZ$11,0)),'Data Analysis'!L:L,u)),
IF(XLOOKUP(Z$11,'Global Rules'!AJ12:AJ1000,'Global Rules'!AI12:AI1000)="Text","",SUMIFS(INDEX('Data Analysis'!$B:$HZ,,MATCH(Z$11,'Data Analysis'!$B$11:$HZ$11,0)),'Data Analysis'!L:L,u)),)),
VSTACK(SEQUENCE(ROWS(d)),XMATCH(u,INDEX(d,,2),0,-1)))),"")

 

[ExcelToDAX]

I would start by using SWITCH instead of all of those IF formulas. for example

SWITCH(XLOOKUP(Z$11,'Global Rules'!AJ12:AJ1000,'Global Rules'!AI12:AI1000),"Text1",SUMIFS(INDEX('Data Analysis'!$B:$HZ,,MATCH(F$11,'Data Analysis'!$B$11:$HZ$11,0)),'Data Analysis'!L:L,u)),"Text2",SUMIFS(INDEX('Data Analysis'!$B:$HZ,,MATCH(F$11,'Data Analysis'!$B$11:$HZ$11,0)),'Data Analysis'!L:L,u)))

 

[Fluff]

What has that got to do with the formula the OP has posted?

 

[Peter Davison]

ExceltoDax - Not sure what Text1 and Text2 is doing - Correct me if I am wrong but it is looking for the word "Text" on the xlookup, if it has "Text" it leaves the sub total cells blank, if it does not have the word "Text" then it uses the calculation to enter the sub totals.
Sorry if I am missing something.

Fluff - Wasn't sure what you meant "What has that got to do with the formula the OP has posted?"
If it is my original query - I was trying to create the final formula by doing it in stages.
Sorry if I have misled in any way.

Thank you both for all the help so far.

 

[Peter Davison]

ExceltoDax - I think I see what you mean now.
I believe the length of the formula would remain the same as using "If"
Is there a benefit of using "Switch" instead of "If"

 

[Fluff]

The switch function will not help with what you are doing.
You could possibly use REDUCE instead of all the IF functions, but that brings it's own complications.

 

[ExcelToDAX]

You are correct Fluff