How to count value with changing margin in WEBI?

Question

​Hi Guys, it sounds easy but it doesn't... Customer got several invoices in specific periods (months). We want to calculate the final amount (total+margin). The problem is that margin is changing during the periods. ​I want to get 7142,76 in one cell (for summary). As you can see it's feasible in table but not in single formula as I am getting #MULTIVALUE I guess the rootcause is margin which variable is quite simple:

=If([Doc Year]="2022" And [Doc Period]>=1 And [Doc Period]<=2) Then 0 Else If ([Doc Year]="2022" And [Doc Period]>2 And [Doc Period]<7) Then 4,5 Else 10

Thanks

The 6835,18 value is calcuated by:

​=Sum([Term Invoice Line Net Amount] ForAll ([Customer];[Doc Year];[Doc Period];[vMargin]))

​Cant figure out how to calculate total with margin...

Answer 1

Maybe someone will use my findings. Finally I tried to focus just on counting the margin amount (307,58) and finally got it:

Sum([Term Invoice Line Net Amount]*[vMargin]/100)

Then final formula is:

=Sum([Term Invoice Line Net Amount] ForAll ([Customer];[Doc Year];[Doc Period];[vMargin]))+Sum([Term Invoice Line Net Amount]*[vMargin]/100)

and it works!

Previously I had #MULTIVALUE as I was counting amount with margin as below:

=Sum([Term Invoice Line Net Amount]*([vMargin]/100))

But the correct formula is:

=Sum([Term Invoice Line Net Amount]*[vMargin]/100)

small diffrence but crucial