Forum Discussion

emre34's avatar
emre34
Regular Visitor
1 year ago

Allocation by Branches

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.

 

Tips and Tricks

3 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    1 year ago

    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

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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