cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SH-VE Regular Visitor
Regular Visitor

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

Capture.PNG

 

1 ACCEPTED SOLUTION

Accepted Solutions
natelpeterson New Contributor
New Contributor

Re: conditional divide

@SH-VE - 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 17
natelpeterson New Contributor
New Contributor

Re: conditional divide

Hi @SH-VE ,

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

Thanks,

Nathan

Community Support Team
Community Support Team

Re: conditional divide

Hi @SH-VE ,

 

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
SH-VE Regular Visitor
Regular Visitor

Re: conditional divide

HI Nathan,

 

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

 

Thanks,

SH-VE Regular Visitor
Regular Visitor

Re: conditional divide

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

SH-VE Regular Visitor
Regular Visitor

Re: conditional divide

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

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

 

Capture.PNGExcel 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

 

natelpeterson New Contributor
New Contributor

Re: conditional divide

@SH-VE - 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

SH-VE Regular Visitor
Regular Visitor

Re: conditional divide

Hello,

  1. Please see the interface of my report which I want to achieve.Capture.PNGThe price/unpriced filter should give WAP only for the quantities which has been Priced. the top-right chart should accordingly show top customers by WAP which has been "priced"
  2. Please find enclosed the cleaned data excel , for the report above. Col D of the excel tags each entry as  priced or unpriced. Please Note Col A for customers. Here, Some quantities for a particular customer (ex. Bai, Col, Ide) is priced while some are unpriced. In such cases the WAP in the final table should calculate WAP only priced quantities (899,1676,1254 respectively). For customers for which every thing is priced (ex. Ken, Kon) it should calculate for total sales/ total quantity (704, 947 respectively). Please , see the desired WAP in Col "I'.

I am not able to attach the working excel. Is it possible to share files. it would be great if you can tell me how to? Meanwhile please see a snaphot of the excel below.Capture 2.PNG

 

Please let me know if you need more info.

natelpeterson New Contributor
New Contributor

Re: conditional divide

@SH-VE - The solution that @v-frfei-msft  provided looks like it should work for your requirement. One thing you could try is to split out the numerator and denominator into 2 additional measures for testing:

 

WAP Numerator =
    CALCULATE (
        SUM ( 'Master Pricing Data'[Sales] ),
        FILTER ( 'Master Pricing Data', 'Master Pricing Data'[Priced/Unpriced] = "Priced" )
    )


WAP Denominator = CALCULATE ( SUM ( 'Master Pricing Data'[Quantity] ), FILTER ( 'Master Pricing Data', 'Master Pricing Data'[Priced/Unpriced] = "Priced" ) )

 

SH-VE Regular Visitor
Regular Visitor

Re: conditional divide

It worked for those entries which have one priced and the other unpriced, like this one.Capture.PNG

 

However , those which have more than one priced , its incorrect, beause its adding up individual WAPCapture.PNG

In secind case WAP should 899. However , its adding up 1250 and 808 to give 2058 as the result.