DAX formula for allocation factor on different levels

[PshemekFLK]

Hello, 

I'm trying to create the "Allocation Factor" formula (last column) that would divide different granularities based on the level of hiearchy:

Basically for each level it should calculate the share within the level above except for the product level where it takes manual input (Granularity 2 and 3).

Thanks.

[Greg_Deckler · Accepted solution]

So, you can generally achieve percentages like that by using something like:

Measure = 

  SUM('Table'[Column]) / CALCULATE(SUM('Table'[Column]),ALLEXCEPT('Table'[Region],'Table'[Distributor],'Table'[Product]))

For sub product level for example.

You would have to string a bunch of these together in a SWITCH statement using ISINSCOPE to determine where you are in the hierarchy.

That's about as detailed as I can get without better information. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[PshemekFLK]

Hello again,

Thanks for recommending ISINSCOPE formula. It helped me moved forward with the issue I had but after many hours of trial and error with DAX I still didn't manage to achieve what I hoped for. Let me explain in detail what I'm working on.

I have 3 fact tables:

bookings,

plan (subregion-product level) for "Sub-Region 2-1"

plan (distributor-product level) for "Sub-Region 2-10"

4 dimensions: calendar, geography, distributor, product

I want to allocate budget to the lowest level of each dimension table based on the actual bookings of prior year (for the levels that are not included in plan data). Below is the standard hierachy used for bookings between all dimensions:

Fact table for plan (subregion-product level) includes "IG-Sub-Region-2" and "Product Family Code" but does not include "Distributor_Name_IG (+)"

Fact table for plan (distributor-product level) includes everything down to "Product Family Code".

Using different granularities and INSCOPE formula I managed to create a partial allocation in the pbix file:

https://files.fm/u/pymrw9w8

1) Allocation of product budget within distributors in "Sub-Region 2-1" is not correct. It uses a split of last year instead of a split of products within subregion budget (each distributor in "Sub-Region 2-1" should have the same % split). It also doesn't sum up correctly: 

2) Totals of allocated budget are not correct. I was thinking about using HASONEVALUE or SUMMARIZE but how can I ensure that the totals will work on all levels and also very important: how can I ensure that if I delete some fields form hierarchy or switch them around that budget allocation will still work for all cases
 

Thanks!

[PshemekFLK]

Would anyone have any suggestions? Is it too complicated to fix with DAX?

Thanks