Forum Discussion

dniedrauer's avatar
dniedrauer
Resolver I
9 years ago

Sum of elements

Here's my issue: I have a formula calculating a refund amount by product for over/under consumption. Idea is refund/charge for more than 10% over/under consumption:    Refund/Charge = (switch(TRUE(...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
9 years ago

You need to post sample/mock data. What I am referring to is to is to have a table in which you have Product.

 

IF(HASONEFILTER([Product]), calculate one way, calculate another way)

 

It sounds like the issue is that your table is correct except for the totals.

dniedrauer's avatar
dniedrauer
Resolver I
9 years ago

Allocation Table 

[vessel]

[chemical]

[allocation] 

 

allocation bridge (combinding items from allocation and main tables) 

[vessel]

[chemical]

[allocation]

 

main table 

[vessel]

[chemical]

[consumption]

[date] 

 

Allocation measure = sum('allocation bridge'[allocation]) 

consumption qty measure = sumx('main',[qty]*[xqty]) 

 

Refund/Charge = (switch(TRUE(),[Consumption Qty]<[Allocation -10%],[Allocation -10%]-[Consumption Qty],[Consumption Qty]>[Allocation +10%],[Allocation-Consumption]+[Allocation]*.10,0))*[Net Price]