Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
mbhunt
Regular Visitor

Divide two columns in a group by.

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? 

 

snip1.jpg

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.

 

snip2.jpg

Thanks much!

1 ACCEPTED SOLUTION

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".

View solution in original post

5 REPLIES 5
mike_honey
Memorable Member
Memorable Member

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?

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.

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".

Looks good.  I think I got it.  Thanks.

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.

Capture.PNG

 Try remaking your calculation as a new measure:New Measure.png

 

 

 

 

 

 

 

 

 

Mike Carlo

www.PowerBI.Tips

mike@powerbi.tips

Mike Carlo ( https://powerbi.tips )
mike@PowerBI.Tips

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors