Allocation by Branches

[emre34]

Hi All,

i have a branch table and the columns are; Country, City, Branch

Also i have a expense table  and the columns are; Date, Branch, Expense type, Amount

Also i have a sales table  and the columns are; Date, Product, Branch,  Sales Amount

The problem is our finance guy make expenses from one dedicated branches in all the cities, so if we have 5 branches in the London he does all the expenses from London#1 Branch and London#3 Branch, If we have 3 branches in the Manchester, he does all the expenses from Manchester#2 branch. 

I want to make a sum off all the expenses by Cities, after i want to allocate them to branches by their sales amount.

How could i succeed it by DAX or by other methods?

Thank you in advance.

[Anonymous]

Hi,

Thanks for the solutions Kedar_Pande  and TomMartens , hello @emre34 ,did the solutions @Kedar_Pande  and @TomMartens  offered help you solve the problem, if they help, you can consider to accept them as solutions , or can  your provide some sample data and the output you want so that can providem more information for you.

Best Regards!

Yolo Zhu

[Kedar_Pande]

@emre34 

Summing Expenses by City

Total Expenses by City = 
CALCULATE(
SUM(ExpenseTable[Amount]),
ALLEXCEPT(BranchTable, BranchTable[City])
)

Calculate Sales Share for Each Branch

Sales Share by Branch = 
DIVIDE(
SUM(SalesTable[Sales Amount]),
CALCULATE(
SUM(SalesTable[Sales Amount]),
ALLEXCEPT(BranchTable, BranchTable[City])
)
)

Allocate Expenses to Branches by Sales Share

Allocated Expenses = 
[Sales Share by Branch] * [Total Expenses by City]

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[TomMartens]

Hey @emre34 ,

this article explains how a value can be allocated to a different granularity: https://www.daxpatterns.com/budget/

If this does not help, create a pbix file that contains sample data but still reflects your semantic model (tables, relationships, calculated columns, and measures). Upload the file to OneDrive, Google Drive, or Dropbox and share the link). If you are using a spreadsheet to create the sample data instead of the manual input method share the spreadsheet as well.
Do not forget to describe the expected output based on the sample data you provide.

Regards,

Tom