Overhead Expnese Allocation by Branch

[emre34]

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 / total sales for country5 ) * sales by branch

but when i wrote below DAX code, it doesn't show the allocated expenses across to the branches in the visual table. 

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

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

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

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


VAR AllocationPerBranch = 
    DIVIDE(Toteloverhead, TotalSalesExcludingHQ) * BranchSales

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

[Anonymous · Accepted solution]

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]

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()
    )