- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

# Divide two columns in a group by.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-15-2017 04:10 PM

Sorry, I know this seems so simple, but I can’t figure it out.

In the following example, how do I take the [ListPriceExtended] and divide it by the [Qty] to get the [AverageListPrice] for each line?

The Qty and ListPriceExtended columns are Sum columns and we are grouping based on SKU. My AverageListPrice is a measure with the following expression:

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

However, the SUM statements are taking the sum of the full table, and not just the sum for each group by.

Here is the breakdown for SKU 70013. The Sum of the ListPriceExtended is $334.48 and the Sum of the Qty is 46, so the AverageListPrice should be $334.48 / 46 for an average list price of $7.27.

Thanks much!

Solved! Go to Solution.

Accepted Solutions

## Re: Divide two columns in a group by.

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-21-2017 05:06 PM - edited 03-21-2017 05:07 PM

I think I know where you went wrong - did you add AverageListPrice as a "New measure" or a "New column"? It looks like "New column", but it should likely be a "New measure".

www.mangasolutions.com

All Replies

## Re: Divide two columns in a group by.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-21-2017 04:35 PM

Your formula looks correct to me. I imagine there must be other data Tables involved in that Table visual, with a missing or incorrect relationship?

www.mangasolutions.com

## Re: Divide two columns in a group by.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-21-2017 04:46 PM

Thanks for the reply.

So is "SUM" supposed to take the Sum of the entire table or just the group by section (which is what I want)?

Thanks.

## Re: Divide two columns in a group by.

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-21-2017 05:06 PM - edited 03-21-2017 05:07 PM

I think I know where you went wrong - did you add AverageListPrice as a "New measure" or a "New column"? It looks like "New column", but it should likely be a "New measure".

www.mangasolutions.com

## Re: Divide two columns in a group by.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-21-2017 07:27 PM

I think Mike has nailed it.. I made a simple demo and made a calculated column and measure. The column reprecates your what your seeing, but the measure actually meets your requirements.

Try remaking your calculation as a new measure:

Mike Carlo

mike@powerbi.tips

mike@PowerBI.Tips

## Re: Divide two columns in a group by.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-22-2017 04:08 AM

Looks good. I think I got it. Thanks.