Forum Discussion

cortlin's avatar
cortlin
New Member
11 years ago
Solved

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 calculat...
  • andre's avatar
    andre
    11 years ago

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

     

andre's avatar
andre
Memorable Member
11 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
    cortlin
    New Member
    11 years ago

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

     

    Thanks much!

     

    // CD