cancel
Showing results for
Did you mean:
Frequent Visitor

## Modify Value in row based on value in other row

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.
1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

## Re: Modify Value in row based on value in other row

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"))
Return IF([Enitity]="Enitity1"&&'Table1'[Category]="Depreciation",[EUR]+a*0.4,
IF([Enitity]="Enitity2"&&'Table1'[Category]="Depreciation",[EUR]+b*0.6))))```

Result:

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
Super Contributor

## Re: Modify Value in row based on value in other row

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Modify Value in row based on value in other row

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

Super Contributor

## Re: Modify Value in row based on value in other row

Hi @ViktorL ,

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Modify Value in row based on value in other row

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

Frequent Visitor

## Re: Modify Value in row based on value in other row

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

Super Contributor

## Re: Modify Value in row based on value in other row

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"))
Return IF([Enitity]="Enitity1"&&'Table1'[Category]="Depreciation",[EUR]+a*0.4,
IF([Enitity]="Enitity2"&&'Table1'[Category]="Depreciation",[EUR]+b*0.6))))```

Result:

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 55 members 1,075 guests
Recent signins: