topic Re: Overhead Expnese Allocation by Branch in DAX Commands and Tips

Overhead Expnese Allocation by Branch

emre34 — Mon, 12 Aug 2024 05:16:27 GMT

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

Re: Overhead Expnese Allocation by Branch

PurpleGate — Mon, 12 Aug 2024 07:04:27 GMT

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

Re: Overhead Expnese Allocation by Branch

Anonymous — Wed, 14 Aug 2024 05:53:18 GMT

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.