Forum Discussion
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 hav...
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.
Anonymous - 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" )
)
It worked for those entries which have one priced and the other unpriced, like this one.
However , those which have more than one priced , its incorrect, beause its adding up individual WAP
In secind case WAP should 899. However , its adding up 1250 and 808 to give 2058 as the result.