Calculated column issue when using division logic

DAX DAX Calculations
1

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:

snip1
snip1.jpg999×437 62.7 KB

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

snip2
snip2.jpg999×415 68.1 KB

Thanks in advance!

2

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

3

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