Forum Discussion

ViktorL's avatar
ViktorL
Frequent Visitor
7 years ago
Solved

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   Con...
Need Help
  • v-danhe-msft's avatar
    v-danhe-msft
    7 years ago

    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

ViktorL's avatar
ViktorL
Frequent Visitor
7 years ago

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's avatar
ViktorL
Frequent Visitor
7 years ago

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

  • v-danhe-msft's avatar
    v-danhe-msft
    Microsoft Employee
    7 years ago

    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