Forum Discussion

LudivineLOU's avatar
LudivineLOU
Helper I
5 years ago
Solved

wrong total

Hello,    I have a wrong total for my column QTY_conf comparing to the table :         Do you know why I have only 550 in total ?    Thank you, Best regards,  Ludivine     
  • v-easonf-msft's avatar
    v-easonf-msft
    5 years ago

    Hi, LudivineLOU 

    The formula is correct for the calculation results of the data in each row of the table.

    But when calculating the total row, it will first find the largest max  [Pur. Doc. Itm]   （60120267700700001）， so you will get the QTY (550) where   [Pur. Doc. Itm] = max  [Pur. Doc. Itm]   （60120267700700001）.

     

    As a  workaround , you can try to create another measure to modify the total：

     

    Qty_conf = CALCULATE(SUM(ZE[SUMQTY]),FILTER(ZE,ZE[Pur.Doc.Itm]=MAX(GR[Pur.Doc.Itm])))
    Qty_conf2 = 
VAR __table = SUMMARIZE(GR,[Pur.Doc.Itm],"__value",[Qty_conf])
RETURN
IF(HASONEVALUE(GR[Pur.Doc.Itm]),[Qty_conf],SUMX(__table,[__value]))

     


     

    Please refer to the sample pbix file attached for more details.

     

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

LudivineLOU's avatar
LudivineLOU
Helper I
5 years ago

It is a formula in interaction with an other table called ZE.

Qty_conf = CALCULATE(SUM(ZE[SUMQTY]);FILTER(ZE;ZE[Pur. Doc. Itm]=MAX(GR[Pur. Doc. Itm])))

 

Thanks

v-easonf-msft's avatar
v-easonf-msft
Community Support
5 years ago

Hi, LudivineLOU 

The formula is correct for the calculation results of the data in each row of the table.

But when calculating the total row, it will first find the largest max  [Pur. Doc. Itm]   （60120267700700001）， so you will get the QTY (550) where   [Pur. Doc. Itm] = max  [Pur. Doc. Itm]   （60120267700700001）.

 

As a  workaround , you can try to create another measure to modify the total：

 

Qty_conf = CALCULATE(SUM(ZE[SUMQTY]),FILTER(ZE,ZE[Pur.Doc.Itm]=MAX(GR[Pur.Doc.Itm])))
Qty_conf2 = 
VAR __table = SUMMARIZE(GR,[Pur.Doc.Itm],"__value",[Qty_conf])
RETURN
IF(HASONEVALUE(GR[Pur.Doc.Itm]),[Qty_conf],SUMX(__table,[__value]))

 


 

Please refer to the sample pbix file attached for more details.

 

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.