cancel
Showing results for
Did you mean: New Member

## Calculating the gross profit

Dear all,

My data source is an excel sheet. it has the following columns (type of revenue/cost, revenue/cost, budget, actual).

this will give us the following data:

Type            Revenue/Cost    Budget     Actual

Revenue      Sales                   1000        1200

Revenue      Other                  3000       3500

DirectCost   Marketing           4000       4500

DirectCost   Staff                      500         750

i would like to calculate the gross profit. it should be the sum or revenues - the sum of costs.

In our example: it should gives me:

Budget=1000+2000+3000-4000-500 = 500

Actual=1200+800+3500-4500-750 = 250

How can i make to achieve the above

Best regards

1 ACCEPTED SOLUTION  Community Champion

```BudgetGrossProfit =
SUMX ( FILTER( ALL ( Table1 ), Table1[Type] = "Revenue"), Table1[Budget] )
- SUMX ( FILTER( ALL ( Table1 ), Table1[Type] = "DirectCost"), Table1[Budget] )

ActualGrossProfit =
SUMX ( FILTER( ALL ( Table1 ), Table1[Type] = "Revenue"), Table1[Actual] )
- SUMX ( FILTER( ALL ( Table1 ), Table1[Type] = "DirectCost"), Table1[Actual] )```

Hope this helps,

David

7 REPLIES 7  Community Champion

```BudgetGrossProfit =
SUMX ( FILTER( ALL ( Table1 ), Table1[Type] = "Revenue"), Table1[Budget] )
- SUMX ( FILTER( ALL ( Table1 ), Table1[Type] = "DirectCost"), Table1[Budget] )

ActualGrossProfit =
SUMX ( FILTER( ALL ( Table1 ), Table1[Type] = "Revenue"), Table1[Actual] )
- SUMX ( FILTER( ALL ( Table1 ), Table1[Type] = "DirectCost"), Table1[Actual] )```

Hope this helps,

David Anonymous
Not applicable

Where do I put this formula please? I have something very similar

Revenue/Cost are in the same column and I want to calculate profit and then display this in a visual e.g. pie chart  Community Champion

The code is used for measures (Modeling tab, New Measure) Anonymous
Not applicable

So I have 5 columns:

- Client Name

- Product

- Revenue Category (where Cost is one of these)

- Project Description

- FY18 Total \$

So I want to the below just not sure how to do it:

Profit = SUM(Revenue Category = Cost) - SUM(Revenue Category != Cost)

And then in the visualisation have this as a total but then add a slicer to filter by Product Name

Possible?  Community Champion

Profit is usually NotCost - Cost, but other than that, yes.

If you are going to be further slicing it, you might want to remove the "ALL" from each code.  This would allow the slicer on product, etc to propogate through.

```Profit =
SUMX ( FILTER(  Table1 , Table1[Type] != "Cost"), Table1[FY18 Total \$] )
- SUMX ( FILTER( Table1 , Table1[Type] = "Cost"), Table1[FY18 Total \$] )```

Try it out and let us know. Anonymous
Not applicable

That's great, thanks! New Member

Hi dedelamn,

it worked perfectly fine.

best regards  