Calculate Max by label

[Ari_Burg]

Hello,

With the following pivot table (Kg_Max, Center,Product and Material):

Center Product Material kg_max
0052 110180 11133 547.205
0052 110180 11147 547.205
0052 110180 11217 547.205
0052 110180 11258 138.187
0052 110180 11534 547.205
0052 110180 11729 85.131


• Kg_Max := MAXX(VALUES([Mix]);calculate([Kg]))
• [Mix] = Center & Product & Material
• [Kg] = Sum(Kg)

I´m looking to correct all materials which have less Kg than the Max by Product. I mean, in this case the Kg_max is 547.205, so I would like to change his value by the Max, in the highlighted rows (138.187 & 85.131).

I have been trying for a while and it has been imposible.

The difference occur because not all materials are used every months, however the product could be made every month.

How can i do it?

Thanks in advance!

 

[scottsen]

If I can understanding correctly, you could just add a calc column that has the max kg_max per Product?

=CALCULATE(MAX(MyTable[kg_max]), FILTER(ALL(MyTable), MyTable[Product] = EARLIER(MyTable[Product])))