Forum Discussion

emre34's avatar
emre34
Regular Visitor
2 years ago
Solved

Overhead Expnese Allocation by Branch

I have sales via branch id and also have a branch id as a headquarter, i want to allocate all the overhead expenses upon sales amounts. so i want to make it in math = ( total overhead for country5 /...
allocation overhead
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi All,
    Firstly, PurpleGate thank your for you solutions!
    And emre34 for you question, I think there is a problem with your TotalSalesExcludingHQ , there is no good way to calculate the total value after the HQ.

     

    We simply remove 'Branch' [BRANCH_ID] to properly sum TotalSalesExcludingHQ.

     

    MeasureAllocationOverheadCN5 = 

VAR currentcountryid = MAX(COUNTRY[COUNTRY_ID])

VAR Totaloverhead = 
    CALCULATE( SUM(OVERHEAD_EXPENSES[OVERHEAD_EXPENSES]), BRANCH[BRANCH_ID] = 80 )

VAR TotalSalesExcludingHQ = 
    CALCULATE( 
        SUM(SALES[SALES_TEAM_A]) + SUM(SALES[SALES_TEAM_B]),Country[COUNTRY_ID]=5,
        REMOVEFILTERS('Branch'[BRANCH_ID])
       
        
    ) 

VAR BranchSales = 
    SUM(SALES[SALES_TEAM_A]) + SUM(SALES[SALES_TEAM_B])

VAR AllocationPerBranch = 
    DIVIDE(Totaloverhead, TotalSalesExcludingHQ, 0) * BranchSales

RETURN 
    IF(
        currentcountryid=5,
        AllocationPerBranch,
0)

     

    If you still have questions, check out the pbix file I uploaded, I hope it helps!

     

    Hope it helps!

    Best regards,
    Community Support Team_ Tom Shen

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

     

PurpleGate's avatar
PurpleGate
Icon for Resolver III rankResolver III
2 years ago

sometimes you need to use FILTER in the measure so it actually can limit the table as expected

 

MeasureAllocationOverheadCN5 = 
var currentcountryid = SELECTEDVALUE(COUNTRY[COUNTRY_ID])

VAR Totaloverhead = 
    CALCULATE( [Overhead Expenses],FILTER(BRANCH, BRANCH[BRANCH_ID] = 80)) –- headquarter branchid is 80

VAR TotalSalesExcludingHQ = 
    CALCULATE( ( [Total Sales Team A] + [Total Sales Team B] ), FILTER(COUNTRY,COUNTRY[COUNTRY_ID] = 5)) –- just want to allocate for country 5

VAR BranchSales = 
    CALCULATE( ( [Total Sales Team A] + [Total Sales Team B] ),FILTER(COUNTRY, COUNTRY[COUNTRY_ID] = 5))


VAR AllocationPerBranch = 
    DIVIDE(Toteloverhead, TotalSalesExcludingHQ) * BranchSales

RETURN 
    IF(
        currentcountryid = 5,
        AllocationPerBranch,
        BLANK()
    )