Power BI subscrating Matrix view.

[michalintive]

Hi all,

I need to create a new column with resoult of subsctracting two columns in Matrix view. I've tried to do it by guides in this community but I wasnt able to do it.

I need to do calculation:

A Revenue - B Project Costs = x

Next I need to:

X / A Revenue = ....

Do you have any ideas how to do that?

Much thanks for your help.

[Anonymous]

Hi,

Are you familiair with the difference between a calculated column and a calculated measure? 

A calculated column can only access columns that are in the same table, or can be accessed via the RELATED() function from the many to one side (so only one value is returned). The result of a calculated column is stored in memory and is calculated on data refresh or when changed.

A calculated measure is calculated each time based on the dimensions it needs to be calculated for. So it is not "part" of a table.

In your situation you can create two measures like

Budget = SUM(ProjectBudget[ProjectBudget])

Cost = SUM(ProjectCost[ProjectCost])

Then create a third measure that does the calculation of choice.

Margin = [Budget] - [Cost]

If you want to have it split per project for example you could import a table that has both budget and cost in the same table and substract them with a calculated column. You can also import a second table that shares the same dimension (Project) and use measures like described. Depends on the granularity and if they use the same dimensions.

Good luck! 

[michalintive]

Is there any possibility to make budget as you wrote but adding a criteria?

For example I need to calculate it in the way

Revenue = SUM(Table1[Value]) & Filter(Table1[Cost_Center_2]='Revenue')

Costs = Sum(Table1[Value] & Filter(Table1[Cost_Center_2]='B Project Costs') ?

Guy which projected this database puted all values in one column revenue and costs.

[v-chuncz-msft · Accepted solution]

@michalintive,

You may refer to the following measures.

Revenue =
CALCULATE ( SUM ( Table1[Value] ), Table1[Cost_Center_2] = "Revenue" )

Costs =
CALCULATE ( SUM ( Table1[Value] ), Table1[Cost_Center_2] = "B Project Costs" )

X =
[Revenue] - [Costs]

Percent =
DIVIDE ( [X], [Revenue] )

[Anonymous]

@v-chuncz-msft, this also works fine but be aware that these Revenue & Cost measures will deliver the same value even for rows with, for example, Gross sales. As long as you don't put those together in a visual its ok and even shorter code. If you only want the Revenue to be calculated for the rows with the Revenue labels use the FILTER() clause.

The FILTER clause will only give the rows with Revenue as label to the calculation of SUM(Table1[Value]). So if there is a row for Revenue and Cost it will only calculates for one row. 

Difference between Revenue with FILTER and without:

The totals are displayed correct but the € 100 at the Cost category is incorrect and can be misleading.

@michalintive, choose your desired solution! The left one is described in @v-chuncz-msft post, the right one in my post. 

[michalintive]

Hi all,

Thanks for all solutions. There is one more thing. Desktop PowerBI shows me now measures after each column:

Is there any way to show columns with measures only after Cost Center 2, in the way as below?

Much apriciate for your help!

---

[michalintive]

Ok, problem solved, I hidden the columns 🙂 Thank you all!!

[Anonymous]

Sure, no problem.

Example based on this table:

Result:

Code:

Revenue = 
CALCULATE(
	SUM(Table1[Value]);
	FILTER(
		Table1;
		Table1[Category]="Revenue"
	)
)

Costs = 
CALCULATE(
	SUM(Table1[Value]);
	FILTER(
		Table1;
		Table1[Category]="Costs"
	)
)

[michalintive]

Thing I need to match in my DB is a project number and organizational unit. I can't add or subscrat column to column.