SUM function gives a wrong higher value

[fbi]

Hello,

I have following fields:

Article No, Branch no, Quantity, cost price

I want to calculate the total value so

SUM(Quantity * cost price) in a KPi

but when I calculate the same line by line and then the total in Excel I can see that the value calculated with the function SUM is much higher and wrong.

I'm a beginner and I have no ieda 

[Or]

I'd suggest including some sample data, but generally speaking, something like this suggests duplication of the data either at the source or through incorrect joins. You should be able to check this by applying e.g. count(Quantity) and see if that matches the expected number of lines.

[PrashantSangle · Accepted solution]

Sum(aggr(your_expression,chart_dim1,chart_dim2))

[fbi]

Here is a simplified data and as you can see the total value in the Kpi is the double of the correct one that you can get summing the values in the table

Please note: in the Sum function FISALD = Quantity and Kostpris = Cost price

[PrashantSangle]

sum(aggr(Sum(FISALD*Kostpris),Article_No,Branch_No))

Try this

[fbi]

Thanks PrashantSangle your solution worked perfectly!

[Or]

If this value is exactly double what it should be, the issue is almost certain to be a duplication of some sort. However, I can't tell you what it is based on the image you've shared. As I mentioned above, you should perhaps count the different fields individually and see if anything is showing up with more than three values in this scenario. If one of these values is from a different table from the others, that would be the first thing I'd check, as duplication is often a result of incorrect keys/joins.

[PrashantSangle]

@fbi  , if it works for you then also click on like of that solution