Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
Solved! Go to Solution.
@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.
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
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.
Will 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
@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
Hello,
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.
Please let me know if you need more info.
@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" ) )
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.
@SH-VE - That calculation should work, so we need to see the text of your calculation and a screenshot of the wrong result in a Power BI visual. You may need to let us take a look at your pbix - you can upload it to dropbox or onedrive. Of course, protect your data.
Cheers!
Nathan
Thanks Nathan,
Here are the visuals
Thw incoreect result on Power BI
The formula used
I will work on sharingthe PBIX.
Thanks
@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.
How can I change existing WAP column to a measure column?
When I tried to create a new measure with formula : WAP1 = DIVIDE([WAP Denominator],[WAP Numerator]); it does 0 as the result.
@SH-VE - Need to see the definition and results of the WAP Numerator and WAP Denominator also.
please see enclosed:
The definition used :
for denominator
@SH-VE - I was really baffled, but then saw the DIVIDE - Numerator needs to be first:
DIVIDE([WAP Numerator], [WAP Denominator])
Hi Nathan,
Thanks a lot for the help. It totally works now.
Thanks again.
Hi @SH-VE ,
Your calculation suggests that you don't want the Sales for row 3 - is that correct?
Thanks,
Nathan
Sorry for the incorrect reply earlier. I want the sales/quantity only for last two rows.
HI Nathan,
That's correct . I want sales for only those that has been "priced".
Thanks,
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |