problem with group /filter

[LudivineLOU]

Hello Everybody, 

I have 2 tables : 

Table ZE : 

Table GR :

MAXConfdate = CALCULATE(MAX(ZE[MAXdateZE]);FILTER(ZE;ZE[Pur. Doc. Itm]=GR[Pur. Doc. Itm]&&ZE[Pur. Doc. Itm]=GR[Pur. Doc. Itm])) --> comes from the tableZE

QTYCONFIRMEDZE = CALCULATE(SUM(ZE[SUMQTYZE]);FILTER(ZE;ZE[Pur. Doc. Itm]=GR[Pur. Doc. Itm]))

--> comes from the tableZE

I would like create a model like this : 

Mois : Maxconfdate  --> By month (table GR)

GR PC (table GR)

2 measures :

QTY_conf = CALCULATE(SUMMARIZE(ZE;ze[SUMQTYZE]);FILTER(ZE;ZE[Pur. Doc. Itm]=MAX(GR[Pur. Doc. Itm])))

qtyconf2 = var __table =SUMMARIZE(gr;GR[MAXConfdate];"__value";[QTY_conf]) return IF(HASONEVALUE(GR[MAXConfdate]);[QTY_conf];SUMX(__table;([__value])))   

--> It is to have the correct total (5100pcs)

Unfortunetaly, as you can see, the result for september is wrong : 

I have 150pcs instead of 550pcs.

It is because it takes into account the Purch item number. 

What is the formula to avoid to take into account the purch item number ?

Thank you, 
Best, 

[Anonymous · Accepted solution]

Hi @LudivineLOU ,

I don't quite understand what value you want. May I ask how 550 is calculated? If you calculate the total, September should be 450.

QTY_conf = CALCULATE(SUM(ZE[SUMQTYZE]),FILTER(ZE,ZE[Pur. Doc. Itm]=MAX(GR[Pur. Doc. Itm])))

This measure you give is the max value of Purch item number. I filter the data for January as shown below, 800 is the maximum value, not the sum.

I found the PBI file from your other posts, and I found that there is no relationship between your two tables. I suggest you create a relationship and set up a both direction relationship so that you don't have to create MAXConfdate and QTYCONFIRMEDZE measures. Because two tables are equivalent to one table.

You only need to write a sum function to get the sum value, or max function to get the max value.

Sum = SUM('ZE'[SUMQTYZE])

Max = MAX('ZE'[SUMQTYZE])

Best Regards,
Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.