Forum Discussion

THar01's avatar
THar01
Frequent Visitor
3 years ago
Solved

Subtract A Measure From An Imported Column Both In The Same Table

I've inherited a BI Desktop file that needs to calculate a particular value that will be called RebateBeforeNetMargin. It needs to calculate the difference between the net margin (the measure) from the imported column named Gross Margin Amount - so basically, Net Margin - Gross Margin Amount. Note that the column is NOT a calculated column. Both the Net Margin and the Gross Margin Amounty currently exist in the same table (SalesTable) and the goal is to insert a new column (RebateBeforeNetMargin) betwen the two existing columns. Here's what the existing table currently looks like:

 

The Net Margin measure is: 

net margin 1 = [Total Sales]- SUM('SalesTable'[Net Cost for]).   The Net Cost For is itself another measure, which in turn is built from another measure and so on. Bit of a tangled mess of measures upon measures. I've tried creating both a measure and calculated column for the desired RebateBeforeNetMargin value but having no luck. I'm either getting circular dependency errors, or just trying to do a simple subtraction function Intellisense doesn't find the Gross Margin Amount column.  For example:
 
RebateB4NetMargin = ('SalesTable'[net margin 1]) - ('SalesTable'[Gross Margin Amount])  Gross Margin Amount is underlined in red with a corresponding error message "Cannot find Gross Margin Amount".  Pretty sure there's some additional function/syntax I need to make this calculation but unsure as to what that would be. Any ideas?
 
Thank you.
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    THar01 If you are creating a measure, you need to wrap column references in an aggregator like MAX, MIN, SUM, etc.

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    THar01 If you are creating a measure, you need to wrap column references in an aggregator like MAX, MIN, SUM, etc.

    • THar01's avatar
      THar01
      Frequent Visitor
      3 years ago

      Thanks Greg, the SUM function did the trick:

       

      RebateB4NetMarg = [net margin 1] - SUM('SalesTable'[Gross Margin Amount])