Extract just sales and just cost of sales from transaction data...new column?

[unclejemima]

I've got a table called trandata with all the transaction data from our POS.

Each record in the trandata table will record the ledger it affects, the the amount of that transaction.

Example:

Account 4000 income, $25

Account 5000 cost of sale, $10

These continue on and on, line by line for hundreds of thousands of transactions.  Each transaction is tranaction is tied to a unique transaction number (DataUnique) so there should technically be minimum one 4000 account entry and one 5000 account entry with every (DataUnique).

The difference between the two would be the profit.

Anything that is a account 4000-4999 is income and anything that is an account 5000-5999 is cost of sale.

I was hoping to make a column for income showing only 4000-4999 account transactions, and a column for cost of sale showing only 5000-5999 account transactions.

Ultimatly allowing me to make a profit calculation of income minus expense.

[vega]

There are many ways to get this done. First, I'd like to get more details of exactly what you want. You want a column that contains the income and a column for cost of sale. My first question is what would you want the income column to look like when the row is an account between 5000-5999? Blank? Zero? Or would you like the column to just contain the sum of income for all rows, regardless of the account number?

[unclejemima]

You know what...i'm not sure 🙂  What do you think would be the most logical?

I was thinking the income column would show zero for the 5000-5999 accounts...and same logic, the CostOfSale column would show zero for all 4000-4999 accounts.

Any accounts that were outside the 4000's and 5000's I would want to exclude from the calculations (as there are 0001-3999 and 6000-9999 accounts as well)

Thank you!

[vega · Accepted solution]

Ok, so if you want to make columns then you can use the following:

Income = 
IF(
    Table1[Account] >= 4000 && Table1[Account] <= 4999,
    Table1[Amount],
    BLANK()
)

CostOfSale = 
IF(
    Table1[Account] >= 5000 && Table1[Account] <= 5999,
    Table1[Amount],
    BLANK()
)

With these two formulas, it will only display for their corresponding rows and will be blank for others. If you want it to be 0 for the outside rows, then just replace the BLANK() with 0.

Now, is there a reason that you are using columns instead of using a measure? With a measure you could have the following:

Income Measure = 
CALCULATE(
    SUM(Table1[Amount]),
    Table1[Account] >= 4000 && Table1[Account] <= 4999,
    VALUES(Table1[Account])
)

CostOfSale Measure = 
CALCULATE(
    SUM(Table1[Amount]),
    Table1[Account] >= 5000 && Table1[Account] <= 5999,
    VALUES(Table1[Account])
)

Using measures will give you some flexibilities when creating reports. It all depends on what you choose to use.

[unclejemima]

If I wanted to plot out the income overlaying the expenses per month how would I go about doing that?

I tried and can't seem to figure it...

I should also make a calc for income minus expense for profit, and then one for gross margin

Would you be so kind as to provide formula's for that?

Thank you!!!

[vega]

How are plotting this per month? The measures I gave you per month should work. Did you connect your date table to your fact table? 

As far as the other calculations, it is very simple:

Profit := [Income Measure] - [CostOfSale Measure]

Gross Margin := DIVIDE([Income Measure] - [CostOfSale Measure], [Income Measure])

[unclejemima]

Made new thread with new question 🙂

http://community.powerbi.com/t5/Desktop/Overlay-income-expense-and-profit/m-p/362598#M163795

[unclejemima]

Fantastic.  I think I'll try both out...but I'm getting the idea the measure is smarter and can achieve same results.

Thank you!!!