Forum Discussion

LudivineLOU's avatar
LudivineLOU
Icon for Helper I rankHelper I
6 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
    6 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.

5 Replies

    • LudivineLOU's avatar
      LudivineLOU
      Icon for Helper I rankHelper I
      6 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
        Icon for Community Support rankCommunity Support
        6 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
      Icon for Helper I rankHelper I
      6 years ago

      Unfortunetaly, I got the same result 😞

       

      MESURE 2 =