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

v-danhe-msft's avatar
v-danhe-msft
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago

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'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
      Icon for Microsoft Employee rankMicrosoft 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