Total amount wrong calculated

[Thomas_G]

If i use the "IF...Then" statement in a pivot table to control how the value must be calculated the total amount of this column is wrong.

The potential sales quantity should be determined based on the order backlog and the currently available quantity.

If the quantity on stock is less then the backlog order quantity of the item the quantity on stock should be used, otherwise the backlog quantity.

=If(
sum({$<Jahr={"$(#=year(today()))"},Monat={"$(#=month(today()))"}>}Stock_Quant) < sum({$<Jahr={"$(#=year(today()))"},Monat={"$(#=month(today()))"}>}BCK_Quant),

//Then
sum({$<Jahr={"$(#=year(today()))"},Monat={"$(#=month(today()))"}>}Stock_Quant)
,

//Else
sum({$<Jahr={"$(#=year(today()))"},Monat={"$(#=month(today()))"}>}BCK_Quant))
)

The total value for the "potential sales quantity" shows the total of the backlog quantity.

But there are many items with less or zero quantity on stock. So the calculated "potential sales quantity" must be far less then the backlog quantity.

If i export the data to excel and calculate the total of the "potential sales quantity" column i get the correct quantities.

That means the calucalted value in the rows are correct but not the total value on top of the pivot table.

Strange is also, this problem only exist if there is no filter group activ.
If i filter the items by item number or item group or any other possible properties the calculation of the total is correct.

[BIAKS]

You can easily solve it like this:

=Sum(Aggr(
If(
Sum(Aggr(sum({$<Year={"$(#=year(today()))"},Month={"$(#=month(today()))"}>}BacklockQuant),Item,Year,Month)) >
Sum(Aggr(sum({$<Year={"$(#=year(today()))"},Month={"$(#=month(today()))"}>}OnStock),Item,Year,Month)),
//Then
Sum(Aggr(sum({$<Year={"$(#=year(today()))"},Month={"$(#=month(today()))"}>}OnStock),Item,Year,Month)),
//Else
Sum(Aggr(sum({$<Year={"$(#=year(today()))"},Month={"$(#=month(today()))"}>}BacklockQuant),Item,Year,Month))
)
,Item,Year,Month))

SS:

[marcus_sommer]

Your comparing logic is - take the smaller value which is also applied on the total-level because 65 is smaller as 74. Like above hinted the totals doesn't respect the results of the row-level else they calculate their own ones. To get the sum of the rows the aggr() needs to wrapped on the outside of row-level calculation - with the right dimension-context. In a case like yours I would apply the following:

sum(aggr(rangemin(OnStock_Expr, BackLog_Expr), Item))

[Thomas_G]

Thanks a lot Marcus,

but is still note able to get this fixed with rangemin.

=sum(Aggr(
rangemin(
Sum(Aggr(sum({$<Year={"$(#=year(today()))"},Month={"$(#=month(today()))"}>}OnStock),Item,Year,Month)),
Sum(Aggr(sum({$<Year={"$(#=year(today()))"},Month={"$(#=month(today()))"}>}OnStock),Item,Year,Month))
)
,Item,Year,Month))

But the post above from BIAKS fixed my problem.

Thanks you very much for your help and the very interesting detailed response!