Forum Discussion

michalintive's avatar
michalintive
Regular Visitor
9 years ago
Solved

Power BI subscrating Matrix view.

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.

  • v-chuncz-msft's avatar
    v-chuncz-msft
    9 years ago

    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] )

8 Replies

  • michalintive's avatar
    michalintive
    Regular Visitor
    9 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
      michalintive
      Regular Visitor
      9 years ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        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"
	)
)