Forum Discussion

cocoloco79's avatar
cocoloco79
Icon for Helper III rankHelper III
5 years ago
Solved

Calculations are retruning incorrect numbers

Hi everyone

 

I'm puzzeled with the dataset I have.

 

For some reason, when I only select "crop" the totals are incorrect, if I select "crop" and "master consignee", totals add up. (image capture 1)

My calculation:

Ordered minus Packed =
SUM('Master'[Ordered]) - SUM('Master'[Packed])
and
Ordered minus Packed x Netweight =
[Ordered minus Packed] * MIN('Master'[Netweight])

 

Also, when I select sweet corn as crop, the calcualtion should be (16x3)x47=2256 pieces, yet returns the wrong result. (image capture2)

My calculation:

InnnerQtyPieces x QtyOuter =
SUM('Master'[InnnerQtyPieces]) *MIN('Master'[QtyOuter])
and
InnnerQtyPieces x QtyOuter x Ordered minus Packed =
[InnnerQtyPieces x QtyOuter] * [Ordered minus Packed]
 
Is there anyone, who could help with this? I suspect a grouping issue as it is a direct query. I can provide the pbix via pricate message if that is helpful? Thank you in advance.
 

Need Help
  • tex628's avatar
    tex628
    5 years ago

    Hi cocoloco79 ,

    You need to make sure the calculation is done individually on each row before it's summarized, otherwise it's going to take the entire quantity (432) and multiply that with the entire remaining quantity (47). 

    Try the calculation below:

    Measure =
SUMX ( 
SUMMARIZE (
Product ,
Product [Product] , 
"Value" , [InnnerQtyPieces x QtyOuter x Ordered minus Packed]
) , 
[Value] 
)


    Br, 
    J

2 Replies

  • tex628's avatar
    tex628
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi cocoloco79 ,

    You need to make sure the calculation is done individually on each row before it's summarized, otherwise it's going to take the entire quantity (432) and multiply that with the entire remaining quantity (47). 

    Try the calculation below:

    Measure =
SUMX ( 
SUMMARIZE (
Product ,
Product [Product] , 
"Value" , [InnnerQtyPieces x QtyOuter x Ordered minus Packed]
) , 
[Value] 
)


    Br, 
    J

    • cocoloco79's avatar
      cocoloco79
      Icon for Helper III rankHelper III
      5 years ago

      Thank you very much. This worked.

      Sorry for my late reply!