Modify Value in row based on value in other row

[ViktorL]

I have financial data and the cost of one entity is supposed to be divided among the others.

All data is in columns as follows:

AccountNum | Sub Category | Category | Entity | Date | EUR

Connected to date table and lookup tables.

Sub Category have values Revenue, Expense and COSG, so I calculate each in a measure as:

Actuals =

VAR Revenue = CALCULATE(SUM('2018 PnL'[EUR]),'2018 PnL'[Brand]<>"TOTALS",NOT(ISBLANK('2018 PnL'[Date])),FILTER('Category List','Category List'[Sub Category]="Revenue"))

VAR Expenses = CALCULATE(SUM('2018 PnL'[EUR]),'2018 PnL'[Brand]<>"TOTALS",NOT(ISBLANK('2018 PnL'[Date])),FILTER('Category List','Category List'[Sub Category]="Expense"))

VAR COSG = CALCULATE(SUM('2018 PnL'[EUR]),'2018 PnL'[Brand]<>"TOTALS",NOT(ISBLANK('2018 PnL'[Date])),FILTER('Category List','Category List'[Sub Category]="COSG"))

RETURN

Revenue+Expenses+COSG

But now I need to divide what's in the Entity "Center" to the other entities, for each category and month. I can get the table I need to add by, for example:

Enitity1_Center_Cost = IF(CONTAINS('2018 PnL','2018 PnL'[Entity],"CENTER"),SUM('2018 PnL'[EUR])*0.555)

So, Entity 1 is supposed to carry 55,5% of the CENTER cost. But I can't figure out how to add this to the values for Entity 1.

[v-danhe-msft]

Hi @ViktorL ,

Could you please offer a sample data to have a test if possible? And coould you please post your desired result?

Regards,

Daniel He

[ViktorL]

Hello @v-danhe-msft 
Thanks a lot for your response.

My data looks like:

AccountNum	Sub Category	Category	Enitity	Date	EUR
61110	Expense	Depreciation	Enitity1	Wednesday, January 31, 2018	2266,12
61120	Expense	General Overhead	Enitity1	Wednesday, January 31, 2018	1679
61110	Expense	Depreciation	Enitity2	Wednesday, January 31, 2018	1197,12
61120	Expense	General Overhead	Enitity2	Wednesday, January 31, 2018	562
61110	Expense	Depreciation	EntityCENTER	Wednesday, January 31, 2018	247
61120	Expense	General Overhead	EntityCENTER	Wednesday, January 31, 2018	26

And I need the EntityCENTER expenses to be split among the other according to rules, let's say
Entity1 carries 40%
Entity2 carries 60%

So I would want (new column TrueEUR, or modify EUR column):

AccountNum	Sub Category	Category	Enitity	Date	EUR	TrueEUR
61110	Expense	Depreciation	Enitity1	Wednesday, January 31, 2018	2266,12	2364,92
61120	Expense	General Overhead	Enitity1	Wednesday, January 31, 2018	1679	1689,4
61110	Expense	Depreciation	Enitity2	Wednesday, January 31, 2018	1197,12	1345,32
61120	Expense	General Overhead	Enitity2	Wednesday, January 31, 2018	562	577,6
61110	Expense	Depreciation	EntityCENTER	Wednesday, January 31, 2018	247	0
61120	Expense	General Overhead	EntityCENTER	Wednesday, January 31, 2018	26	0

[v-danhe-msft]

Hi @ViktorL ,

I could not understand your rules, could you please explain more about how the 2266.12 convert to 2364.92 with your rules:

And I need the EntityCENTER expenses to be split among the other according to rules, let's say
Entity1 carries 40%
Entity2 carries 60%

AccountNum	Sub Category	Category	Enitity	Date	EUR	TrueEUR
61110	Expense	Depreciation	Enitity1	Wednesday, January 31, 2018	2266,12	2364,92
61120	Expense	General Overhead	Enitity1	Wednesday, January 31, 2018	1679	1689,4
61110	Expense	Depreciation	Enitity2	Wednesday, January 31, 2018	1197,12	1345,32
61120	Expense	General Overhead	Enitity2	Wednesday, January 31, 2018	562	577,6
61110	Expense	Depreciation	EntityCENTER	Wednesday, January 31, 2018	247	0
61120	Expense	General Overhead	EntityCENTER	Wednesday, January 31, 2018	26	0

Regards,

Daniel He

[ViktorL]

Hello @v-danhe-msft , 

For example, Entity1 Depreciation is 2266,12 EUR. Now it should also carry 40% of the CENTER expense for Depreciation (which is 247 EUR).

40% of 247 EUR = 98,8 EUR.

TrueEUR for Entity1 Depreciation is therefore: 2266,12+98,8=2364,92

[ViktorL]

@v-danhe-msft  Hello again, did the explanation make sense? Thanks in advance!

[v-danhe-msft · Accepted solution]

Hi @ViktorL ,

Based on my test, you could refer to below formula:

True EUR = var a=CALCULATE(SUM(Table1[EUR]),FILTER('Table1','Table1'[Category]="Depreciation"&&'Table1'[Enitity]="EntityCENTER"))
var b=CALCULATE(SUM(Table1[EUR]),FILTER('Table1','Table1'[Category]="General Overhead"&&'Table1'[Enitity]="EntityCENTER"))
Return IF([Enitity]="Enitity1"&&'Table1'[Category]="Depreciation",[EUR]+a*0.4,
        IF([Enitity]="Enitity2"&&'Table1'[Category]="General Overhead",[EUR]+b*0.6,
        IF([Enitity]="Enitity1"&&'Table1'[Category]="General Overhead",[EUR]+a*0.6,
        IF([Enitity]="Enitity2"&&'Table1'[Category]="Depreciation",[EUR]+b*0.6))))

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He