topic Re: Granularity issues to get Total Budget for subcategories in DAX Commands and Tips

Granularity issues to get Total Budget for subcategories

lkaneff — Wed, 11 Jan 2023 15:16:25 GMT

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

Re: Granularity issues to get Total Budget for subcategories

amitchandak — Wed, 11 Jan 2023 15:59:34 GMT

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

Re: Granularity issues to get Total Budget for subcategories

lkaneff — Wed, 11 Jan 2023 16:46:49 GMT

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.

Re: Granularity issues to get Total Budget for subcategories

amitchandak — Thu, 12 Jan 2023 03:40:24 GMT

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

Re: Granularity issues to get Total Budget for subcategories

lkaneff — Thu, 12 Jan 2023 15:29:53 GMT

Thanks! That was extremely helpful!