Margin % Calculation Column

[cortlin]

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

[andre · Accepted solution]

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]))

[Anonymous]

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])) * 100

Result: 29.18%     Correct!

 So I am confused.  Is the issue in the model or the way row level calculation in DAX works?

[cortlin]

That worked like a charm. Now to understand why microsoft recomends columns vs. measures in this capacity? 

Thanks much!

// CD

[andre]

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

http://businessintelligist.com/2014/11/21/tutorial-how-to-create-a-star-schema-model-in-power-bi-and...

[Greg_Deckler]

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.

[cortlin]

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. 

https://www.evernote.com/shard/s101/sh/fa88aabc-7987-44a1-8332-83dc9933122d/a7005287056a1553e3c21931...