Calculating the gross profit

[Fady_Sayegh]

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      Sponsor              2000          800

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

Thank you for your help

Best regards

Fady Sayegh

[dedelman_clng · Accepted solution]

Hi @Fady_Sayegh

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]

I used the same thing:

Gross_Proft = SUMX(FILTER(ALL(data[Description]),data[Description]="Net Sales"))-SUMX(FILTER(ALL(data[Description]),data[Description]="CoGS"))

but its gives the following error:

Too few arguments were passed to the SUMX function. The minimum argument count for the function is 2.

[Anonymous]

basically, i think it has to do with the brackets. 

[Anonymous]

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 

[dedelman_clng]

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

[Anonymous]

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?

[dedelman_clng]

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]

That's great, thanks!

[Fady_Sayegh]

Hi dedelamn,

it worked perfectly fine.

Thank you for your help

best regards

Fady Sayegh