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 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!