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

 

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.
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

6 Replies

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

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

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

       

      My data looks like:

       

      AccountNumSub CategoryCategoryEnitityDateEUR
      61110ExpenseDepreciationEnitity1Wednesday, January 31, 20182266,12
      61120ExpenseGeneral OverheadEnitity1Wednesday, January 31, 20181679
      61110ExpenseDepreciationEnitity2Wednesday, January 31, 20181197,12
      61120ExpenseGeneral OverheadEnitity2Wednesday, January 31, 2018562
      61110ExpenseDepreciationEntityCENTERWednesday, January 31, 2018247
      61120ExpenseGeneral OverheadEntityCENTERWednesday, January 31, 201826

       

       

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

      AccountNumSub CategoryCategoryEnitityDateEURTrueEUR
      61110ExpenseDepreciationEnitity1Wednesday, January 31, 20182266,122364,92
      61120ExpenseGeneral OverheadEnitity1Wednesday, January 31, 201816791689,4
      61110ExpenseDepreciationEnitity2Wednesday, January 31, 20181197,121345,32
      61120ExpenseGeneral OverheadEnitity2Wednesday, January 31, 2018562577,6
      61110ExpenseDepreciationEntityCENTERWednesday, January 31, 20182470
      61120ExpenseGeneral OverheadEntityCENTERWednesday, January 31, 2018260

       

      • v-danhe-msft's avatar
        v-danhe-msft
        Microsoft 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