3 Replies Latest reply on Nov 21, 2018 2:45 PM by Deepak Rai

    Using Aggregated Data in If Statements

    Eoghan Lyons

      I have been trying to resolve a problem with a calculated field. I have a set of aggregated data from which I calculate certain factors.

       

      The specific fields are:

       

      Number of Products:      self explanatory

      Total Price:                     This is the aggregated Total for all the selling prices in the product count for that period.

      Total Full Price:              This is the aggregated Total for all the full prices in the product count for that period (this equals the above field if the discount is 0). 

       

      I then calculate:

      Price:                              I divide Total Price by Number of Products. This gives me the average selling price per product.

      Full Price:                       I divide Total Full Price by Number of Products. This gives me the average selling price per product.

      Discount %:                    I have a similar formula here

                                              Formula: ((sum([Total Full Price])/sum([Number of Products]))-(sum([Total Price])/sum([Number of Products])))/(sum([Total Full Price])/sum([Number of Products]))

       

      The problem I have is that I can't use these formulas in an if statement. I need to do the calculation so that I'm applying the discount formula for when [Category] = XXX

       

      I can't use an aggregated measure and an if statement together. Does anyone have any advice on how to resolve?