Forum Discussion
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 / 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()
)
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 ShenIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
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() )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 ShenIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
