cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Shalin
Frequent Visitor

Calculating margin % by product group

We have a Dynamics 365 database feeding into BI, which has sales invoice lines and COG's for all of the items we sell. Each item falls into a different product group, and I would like to calculate margin by product group. At a line level it's easy enough to have a column as follows:

Margin = IF(SalesInvoiceLineMargin[LINEAMOUNTMST]=0,0,(SalesInvoiceLineMargin[LINEAMOUNTMST])-(SalesInvoiceLineMargin[CAPCOSTVALUE]))/(SalesInvoiceLineMargin[LINEAMOUNTMST])
 
Where the IF function removes the errors thrownup by 0 sales price invoices. The rest of the formula is a simple margin calculation taking sales lines less COGS and divide by sales lines. For an individual item or invoice, this works fine.
 
However when I try and analyse by product group, I use the same formula and have a table with product group and then this column next, Because it is summarising the column, by default it wants to use the average of the margin per line. That is not the correct number however, because the average of all margins across all lines does not equal the weighted average of all margins. Especially when you have 0% or 100% margin lines skewing the numbers. Onedrive link to an excel file explaining the difference:
 
My question is, is there a way to get around this? Or do I need to have a much more complicated calculation column which is saying sum sales lines for this product group, less COGS for the product group, divide by sales for the product group. And then do one of those for each product group?
Ideally I would want the final margin column to be a calcuation based on the cell in the table on the visualisation. Not on the entire underlying table. So the last column is literally the equivalent of (Cell A1-Cell B1/Cell A1)
 
Thanks
2 REPLIES 2
Super User IV
Super User IV

Re: Calculating margin % by product group

@Shalin hey so you are looking for result in E11 and E12, correct?






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Shalin
Frequent Visitor

Re: Calculating margin % by product group

Yes looking for E11/E12 rather than B15/B16 which is what BI is currently showing

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors