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

the problem is hasonevalue applies to columns, not measures. 

 

Using a column isn't realistic in this case. I'd like something that does the same thing for a measure. 

Greg_Deckler's avatar
Greg_Deckler
Community Champion
9 years ago

Use HASONEFILTER.

 

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