Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

conditional divide

Hi all,

 

I need to calculate WAP by divding Sales by quantity only for columns which has been categorized as priced. The quantity under unpriced should not be added.

In the WAP column below I have entered following formula:

WAP = DIVIDE('Master Pricing Data'[Sales],'Master Pricing Data'[Quantity]). Hence, It is dividing sum of all three quantities by total sales.
Kindly help , so that the divide is as follows: WAP= (Sales (Row2)+Sales (Row1))/((Quantity (Row2)+Quantity(Row3))).
 
Thanks

 

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Anonymous - It looks like you're creating WAP as a Calculated Column. It needs to be a Measure.  Also, Numerator and Denominator need to be Measures.

17 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    Hi Anonymous ,

     

    We can use FILTER function here to get the excepted result.

     

    WAP =
VAR sales =
    CALCULATE (
        SUM ( 'Master Pricing Data'[Sales] ),
        FILTER ( 'Master Pricing Data', 'Master Pricing Data'[Priced/Unpriced] = "Priced" )
    )
VAR quantity =
    CALCULATE (
        SUM ( 'Master Pricing Data'[Quantity] ),
        FILTER ( 'Master Pricing Data', 'Master Pricing Data'[Priced/Unpriced] = "Priced" )
    )
RETURN
    DIVIDE ( sales, quantity )

     Regards,

    Frank

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks for the email. I entered the following function in the calculated Col "WAP"

      However, Its still calculating the quantity for "unpriced" row. Please see the example of Ideal roffing as enclosed. P

       

      Excel version Pivot of the background dataWill you suggest to create a helper (dummy column) or kindly suggest if there is somthing incorrect in the manner i have entered the function. 

       

      Thanks

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Anonymous - It's difficult to see what the problem is with your example. Could you supply a dataset, or image of what you're trying to accomplish, including the measure and description of what is wrong with it?

        Thanks,

        Nathan

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Anonymous ,

    Your calculation suggests that you don't want the Sales for row 3 - is that correct?

    Thanks,

    Nathan

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      HI Nathan,

       

      That's correct . I want sales for only those that has been "priced".

       

      Thanks,

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Sorry for the incorrect reply earlier. I want the sales/quantity only for last two rows.