How to solve the SUMIFS()/XLOOKUP() in power query?

[teziboy]

Hi Everyone,

I am using power query for the very first time and facing an issue in developing a formula of SUMIFS.
The formula will be =SUMIFS($T$2:$T$1048576,$D$2:$D$1048576,D2,$W$2:$W$1048576,W2)/XLOOKUP(W2,VEH!D:D,VEH!E:E)
Could you please assist to develop the formula in power query??
Thanks in advance.

Regards
Shazzad

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: How to solve the SUMIFS()/XLOOKUP() in power query?
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[teziboy]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: How to solve the SUMIFS()/XLOOKUP() in power query?
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Hi Fluff, I will surely remember the issue in next time.

 

[teziboy]

Hi,
To get the file, please click here.
In sheet1, of the file, X column, capacity utilization will be calculated. And I was trying to implement the formula using power query since the data set will be huge and using the formula will slow down the operation in the file. The formula, I am trying to write in power query, is

=SUMIFS($T$2:$T$73,$D$2:$D$73,D2,$W$2:$W$73,W2)/XLOOKUP(W2,Sheet2!A:A,Sheet2!B:B)

Thanks.