Forum Discussion

MojoGene's avatar
MojoGene
Post Patron
10 years ago

Question about aggregating two measures

Not sure if measures can be used in this manner and hoping to get some advice.

 

I am working with income and expenses from a typical general ledger transactions table (TABLE). Each type of transaction in the general ledger table has an identification number, "GLID," with income transactions falling between GLID=40000 and 49999 and expense transactions falling between GLID=50000 and 65000.

 

I created a measure called "Current Income" = SUM(TABLE[CreditAmount)) - SUM (TABLE[DebitAmount]), which is filtered for the range of transactions between GLID=40000-49999. This works perfectly and the income results are as expected. I also created a measure for "Current Expenses" = SUM(TABLE[DebitAmount]) - SUM(TABLE[CreditAmount], which is filtered for GLID=50000-65000. This also works perfectly and the expense results are as expected.

 

What I am trying to do now is to create a measure for "Net Profit" subtracting the "Current Expenses" from the "Current Income." Logically, this would be the sum of the above two measures: SUM(TABLE[Current Income]) - SUM(TABLE[Current Expenses]). But the SUM function is looking for a column, not a measure.

 

I tried Net Profit = SUM(TABLE[CreditAmount]) - SUM(TABLE[DebitAmount]) filtered for the entire range of GLID numbers from 40000 to 65000, but again no luck.

 

So, here I am with tin cup in hand. Any help appreciated.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    MojoGene You already have the sum of both numbers. Just subtract the two measures for your net profit...

     

    Net Profit := [Current Income] - [Current Expenses]