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
dniedrauer's avatar
dniedrauer
Resolver I
9 years ago

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

 

Nope. Still asks me for a column. 

dniedrauer's avatar
dniedrauer
Resolver I
9 years ago

The original formula is a composite of measures built from columns in multiple tables. 

 

I need the total to be in terms of product. Add line 1, 2, 3, etc. just like I could do in excel. 

 

Is there an option for "aggregate by product" or something? 

  • 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]