Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to 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
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
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 |
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |