Calculated column issue when using division logic

Hey guys,

Maybe someone here can help me with my dilemma… I can’t seem to figure out how I can get the AverageListPrice for each row using the Qty and ListPriceExtended columns. I did the measure below for the AverageListPrice, but the SUM statements are taking the sum of the full table and not just the sum of the SKU:

AverageListPrice = SUM(MarkDownRaw[ListPriceExtended]) / SUM(MarkDownRaw[Qty])

Here is the table that I’m working on:

I also included a sample table with breakdown of a SKU:

Thanks in advance!

Try using the DIVIDE function your measure, and not the /. This would definitely be a best practice that Sam would suggest. Also you more than likely need SUMX and not SUM. In future posts it also helps tremendously if you post your PBIX file, or a sample file if data is sensitive. Try the following measure:

AverageListPrice = DIVIDE(SUMX(MarkDownRaw, MarkDownRaw[ListPriceExtended]) , SUMX(MarkDownRaw, MarkDownRaw[Qty]) ) )

Thanks

Enterprise%20DNA%20Expert%20-%20Small

Yes as per Jarrett mentions above here, it looks like all you need is better function selection.

Definitely use DIVIDE instead of /

Also a stronger understanding of SUMX and iterating function is required here.

I would run through the Mastering DAX Calculations course as soon as you can. There is a whole section on this material and these techniques

See below.

Check out these specific tutorials as well.

http://portal.enterprisedna.co/courses/mastering-dax-calculations/lectures/2000592

http://portal.enterprisedna.co/courses/mastering-dax-calculations/lectures/2399489

Thanks
Sam