Granularity issues to get Total Budget for subcategories

[lkaneff]

I need to calculate a Budget total for each category but in order to do that I have to disaggregate the Category Budget Totals to be at the SubCategory level.  I was wondering how I would be able to do that.  I thought about using ALLEXCEPT but that gave me the total budget for all categories.  So I'm stumped.  Please help me with this problem.  Thanks. 

Category.   I have attached an image of my table

[lkaneff]

Thanks!  That was extremely helpful!

[amitchandak]

@lkaneff , I think it better to have many to many join between budget and product and then have a measure like

Sumx(Sumamrize(Budget, Product[Category], Budget[Value]), [Value])

not need of category. Try if this can work

[lkaneff]

My total budgets measure which is just sum[budget[budget] gives me the same answer.  I want to know what the budget for each subcategory is. Like how much of the budget Bike Racks would be.  I have to use the Sales history to get that answer.  Not sure what the best approach is.  If you can think of how to solve this for me that would be great.  Appreciate your help.  

[amitchandak · Accepted solution]

@lkaneff , That would need allocation as the budget is at the category level

Divide( Sumx(Sumamrize(Budget, Product[Category], Budget[Value]), [Value]), countrows(Sumamrize(Budget, Product[Category],Product[Sub Category]) ) )