Forum Discussion
Margin % Calculation Column
Hello,
I'm new here, but was wondering if anyone could point me in the right direction as to how to perform a Margin % calcuation. I obviously have two columns "Sales" and "Cost". A simple calculation is =(Sales-Cost)/Sales. When I enter this formula into a new column and select the percentage format in Power BI desktop the calculation is way off! Here are some example numbers:
Sales: 3,610,000
Cost: 2,100,000
Net: 1,510,000
Expected Margin %: 41.82
Actual Returned: 103,600.13%
Any help would be greatly appreciated.
Best // CD
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]))
6 Replies
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]))
So if I still have an issue where the Sales and Cost columns from the DB table are correct, but the margin measure using the DIVIDE() operator is still returning an anomolous value, I suould suspect my model, right?
July Release PBI, SS 2016 SP2
The model is two tables GLItemLevel and DaxDates with a one to many from the Date table to ItemLevel.
In SSMS I get a correct margin in SQL, but in Power BI i get the following
My column values:
Sales Cost
98 -70 So Margin should be 29.2%
Things that I tried and some worked and some did not.
MarginColumn = IF([Total Sales] = 0, 0, ([Total Sales] + [Total Cost] ) / [Total Sales])
Result: This resulted in a circular reference error.MarginColumn = DIVIDE(SUM(ItemLevel[Sales Amount]) - SUM(ItemLevel[Cost]), Sum(ItemLevel[Sales Amount]))
Result: -134 Way off!This column worked
Margin % = IF([Total Sales] = 0, 0, ([Total Sales] + [Total Cost] ) / [Total Sales])
Result: 29.2% Correct!This column did not work
Margin = DIVIDE(SUM(ItemLevel[Sales Amount]) - SUM(ItemLevel[Cost]), Sum(ItemLevel[Sales Amount]))
Result: -13362.9% Way off!This measure did not work:
MeasureMargin = DIVIDE((SUM(ItemLevel[Sales Amount]) - SUM(ItemLevel[Cost])), SUM(ItemLevel[Sales Amount]))
Result: 1.71 Way off!This Measure worked:MeasureMargin % = IF(SUM(ItemLevel[Sales Amount]) = 0, 0, (SUM(ItemLevel[Sales Amount]) + SUM(ItemLevel[Cost])) / SUM(ItemLevel[Sales Amount])) * 100Result: 29.18% Correct!So I am confused. Is the issue in the model or the way row level calculation in DAX works?That worked like a charm. Now to understand why microsoft recomends columns vs. measures in this capacity?
Thanks much!
// CD
if you are new to modeling with Power BI, you may want to go through this tutorial. If frames up some basic concepts that you may find useful
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.
