Calculating margin % by product group

[Shalin]

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:

https://1drv.ms/x/s!AraVdhhvWK6aiGWvDwDGsvo-mWui

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

[parry2k]

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

[Shalin]

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