Allocate expenses by month to department and sub departments

[prakritnepal-cn]

I am trying to allocate shared expenses to sub departments. my data structure:

Expense category	Sub-department	Department	Month-year	Expenses
cat1	sub1	dept1	Jan-21	10
cat2	sub1	dept1	Jan-21	10
cat1	sub2	dept1	Jan-21	10
cat2	sub2	dept1	Jan-21	10
cat1	sub3	dept1	Jan-21	10
cat2	sub3	dept1	Jan-21	10
cat1	sub4	dept2	Jan-21	10
cat2	sub4	dept2	Jan-21	10
cat1	sub5	dept2	Jan-21	10
cat2	sub5	dept2	Jan-21	10
cat1	shared	dept1	Jan-21	50
cat2	shared	dept1	Jan-21	50
cat1	shared	shared	Jan-21	50
cat2	shared	shared	Jan-21	50

I have departments and sub-departments structured in the format above. I have a seperate revenue and COGS calculation by month (Through date table). The above table is connected to date table by date (one date per month), sub department to another table for that sub-department connection. 
I have calculated % of total revenue by department and % of total revenue of everything which looks something like this:

department	sub	% of department revenue	% of total revenue
dept1	sub1	20%	20%
dept1	sub2	40%	20%
dept1	sub3	40%	20%
dept2	sub4	50%	20%
dept2	sub5	50%	20%

% of department revenue formula: 

'financial measures'[recognized revenue]/CALCULATE('financial measures'[recognized revenue],ALLEXCEPT('practice areas','practice areas'[Department]))

% of total revenue formula: 

'financial measures'[recognized revenue]/CALCULATE('financial measures'[recognized revenue],ALLEXCEPT('practice areas','practice areas'[sub-department]))

I am struggling with allocating the shared expense pieces. The allocation would be two folds, one for just the department, one for shared for both departments. It should look something like this based on revenue splits if both shared expense total to $100

department	sub	% of department revenue	% of total revenue	Department shared allocation	Total shared allocation
dept1	sub1	20%	20%	$20	$20
dept1	sub2	40%	20%	$40	$20
dept1	sub3	40%	20%	$40	$20
dept2	sub4	50%	20%	$0	$20
dept2	sub5	50%	20%	$0	$20
Total		200%	100%	$100.00	$100

At the end of the day, I am trying to have sub department expense + departmental shared expense allocation+ total shared expense allocation. 

How should I move forward to achieve this? Thanks a lot

[prakritnepal-cn · Accepted solution]

Figured out the solution, 

Created a reference table from the original table of expenses and filtered on only shared expenses and linked that to only date table, no joins to expenses or department tables ( so it does not auto filter). 

Model: 

Created two seperate dax for calculating individual departments shared expenses and used the % of revenue formula stated in the original post:

CALCULATE(SUM('expenses-shared'[Expenses]),FILTER('expenses-shared','expenses-shared'[Department]="dept1"),

TREATAS(VALUES('practice areas'[Department]),'expenses-shared'[Department])) * 'OH measures'[% of department revenue]

This gives me shared expenses allocated for department one only split out by month because of date table join and treatas join in the formula with the department (no joins in the model for department between shared expenses and the main expenses table). Exact same thing for dept2. 

Same concept for shared expenses across both departments but without the treatas connection to department as this needs to be allocated across all sub departments: 

CALCULATE(SUM('expenses-shared'[Expenses]),FILTER('expenses-shared','expenses-shared'[Department]="Shared-services")) * 'OH measures'[% of total revenue]

[v-yetao1-msft]

Hi @prakritnepal-cn 

Could you provide the data in the other tables? The information you provide is not enough to create a calculation formula. If possible, it is recommended to provide the pbix file (delete sensitive information), so that the table data and table relationships can be seen more clearly, so as to better deal with the problem.

Best Regard

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.