Forum Discussion
Margin % Calculation Column
I would not be creating Margin % calc as a column, you should create it as a measure and then you don't have to worry about how Margin % is being aggregated which is what you may be seeing when your numbers are off.
Also, you should use the DIVIDE() function to make sure you don't get affected by zeros
https://msdn.microsoft.com/en-us/library/jj677276.aspx
so the calc should be something like this:
Margin % = DIVIDE(SUM(mytable[revenue]) - Sum(mytable[cost]), Sum(mytable[Revenue]))
I used the following formula with the % turned on and it was OK:
GM = (sales[Sales] - sales[Cost]) / sales[Sales]
This was using Power BI Desktop and adding a new column. Did you insert a column or a measure?
I tried it with the Sales and Cost columns being whole numbers, decimal numbers, text and currency and the calculation was correct each time.
Hi,
Thanks for the reply. I've checked, [Total] and [Total Cost] columns are set to data type 'Decimal Number' with Format set to 'Currency'. When I add a new column for GM% again Data Type is set to 'Decimal Number' and Format is set to 'Percantage".
I've included a screenshot to my formula, I've just hiden the data source for privacy.