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

[shazzad]

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??
Thanks in advance.

Regards
Shazzad

 

[p45cal]

As before:

You are more likely to get an answer if you upload your workbook with enough data in it.

 

[GraH - Guido]

Hi,
That's actually a bad idea to try this. You should learn the basics of PQ first and understand what it is meant to do. It is not a substitute for spreadsheet functions.
Any lookup would be a merge table, apart a lookup for approximate match. SUMIFS is a group by.

 

[shazzad]

Hi,
To get the file, please check the attached file.
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.

 

[shazzad]

As before:

Done as you suggested.

 

[shazzad]

Hi,
That's actually a bad idea to try this. You should learn the basics of PQ first and understand what it is meant to do. It is not a substitute for spreadsheet functions.
Any lookup would be a merge table, apart a lookup for approximate match. SUMIFS is a group by.

Hi
Thanks for your valuable input. Actually, I agree with you but since I am very new in this field. I don't want to loose my interest by any means. So I am trying to get the solution. And I can confirm you that after getting you solution, I will dissection the formula and learn it for future.
Thanks again for your kind words.

 

[AlanSidman]

I think I figured out what you are looking to do. You will need to group the first table (Sum) on Date and Vehicle. Then you will need to merge (join) the two tables on the Vehicle and then divide the summed table by the merged values. See the attached and look at the Mcodes for each of the 3 queries.

 

[shazzad]

Hi @AlanSidman , Thank you so much for your contribution. You have solved the problem but in some different way. I actually need to reflect the value of mentioned formula in each cell of Column X. I need to do this way because I will have to do Pivot table after that. But in you solution, due to using Group By all other columns are lost. I hope I can make you understand the point. Please let me know if you have any question.

 

[GraH - Guido]

Hi
Thanks for your valuable input. Actually, I agree with you but since I am very new in this field. I don't want to loose my interest by any means. So I am trying to get the solution. And I can confirm you that after getting you solution, I will dissection the formula and learn it for future.
Thanks again for your kind words.

Hi, by no means I want to keep you away from PQ, on the contrary. Sorry if I gave you that impression.
I did show the path towards the solution. Merge queries and group by, what @AlanSidman was so kind to offer.

Attached an extension of his queries.

 

[shazzad]

Hi @GraH - Guido , Thanks for you time. But to be honest, I need the calculated capacity utilization in the Column X of Sheet1 because I have to present different dynamics that will allow to analyze data from different point of view. I do respect your time and effort but it would be really great for me having that solution accordingly. Thanks again for your suggestion and effort.

 

[GraH - Guido]

Sorry, I do not understand anymore what you are asking.
I have the same result as the one in your sample, and inside PQ you have the calculation present.
You said "I will have to do pivot table". I simply loaded the PQ as pivot and not as table. Else you load twice the data.

 

[GraH - Guido]

For illustration purposes only, as my DAX formula writing skills are far from ideal.
You can even go without PQ and use a PowerPivot.
Load both ranges to the data model.
Create the relationship.
Use a measure.

Note I used a calculated column [Vehicle HCFT], fx:=RELATED(vehicles[HCFT]), to make it easier.

if(HASONEFILTER(facts[Veh No.]);
divide(CALCULATE(sum(facts[LCFT]);ALLEXCEPT(facts;facts[Veh No.]));average(facts[Vehicle HCFT]));blank())

I know it's possible to write it without using the calculated column, I always struggle to do.

 

[shazzad]

For illustration purposes only, as my DAX formula writing skills are far from ideal.
You can even go without PQ and use a PowerPivot.
Load both ranges to the data model.
Create the relationship.
Use a measure.
View attachment 79277
Note I used a calculated column [Vehicle HCFT], fx:=RELATED(vehicles[HCFT]), to make it easier.

if(HASONEFILTER(facts[Veh No.]);
divide(CALCULATE(sum(facts[LCFT]);ALLEXCEPT(facts;facts[Veh No.]));average(facts[Vehicle HCFT]));blank())

I know it's possible to write it without using the calculated column, I always struggle to do.
View attachment 79278

Hi @GraH - Guido ,
Thank you so much. I was actually trying to look into the data like your solution.
Again thanks for your time and effort.

Shazzad