Forum Discussion

LudivineLOU's avatar
LudivineLOU
Helper I
6 years ago
Solved

problem with group /filter

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, 
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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.