Allocation of cost with Dax based on multiple criteria

[PowerEti]

Hello, 

I am new to this forum and happy to be part of the community!

I have put a link to an excel file in one drive for what I am trying to get in Power BI. I need to get the allocation by adding a column to the table in the data model (The table is the sheet 'Before'). The column to be added to the data model is Allocation by month in the sheet 'After'.

Link to the excel file

The aim is to allocate ADMIN DIRECT to the direct channels (Door to door & Mail order Underlined in blue) taking into account the initial account repartion and an allocation percentage by Channel (in the column Alloc %). 

Since in the raw data, the days with Admin Direct amount are not necessarily on the same day than other direct channel line, the repartition needs to be done at the month level. 

The same logic is to be applied to ADMIN INDIRECT into the Indirect channels wholesale, department store, retail (Underlined in orange). 

Thanks,

[Victoria_Bouts]

Hi,

Thanks a lot for solution! Used in my model and it works!!! The only problem is that it takes only single month a

 My model is based on Calendar.Date, BUBA - is conc of BU(business unit) and BA(business area) and formulas which I use are

ACT MBUBA = SWITCH([Selected Calc],

1, [ACT Month MBUBA],

2, [CYTD MBUBA])

ACT Month MBUBA = CALCULATE(SUM('NEW BUBA'[total]),

    FILTER ('NEW BUBA', 'NEW BUBA'[MonthNo]<= SELECTEDVALUE ('MonthSel'[MONTH NUMBER]) && 'NEW BUBA'[period]=SELECTEDVALUE(PeriodSel[PERIOD])))

CYTD MBUBA = CALCULATE(TOTALYTD(SUM('NEW BUBA'[total]), 'Calendar'[Date]),'NEW BUBA'[period]<= SELECTEDVALUE (PeriodSel[PERIOD]))

COMM1 = CALCULATE('NEW BUBA'[ACT MBUBA], KEEPFILTERS('NEW BUBA'[BU]="COMM"&&'NEW BUBA'[period]=SELECTEDVALUE(PeriodSel[PERIOD]) && 'NEW BUBA'[MonthNo]<= SELECTEDVALUE ( 'MonthSel'[MONTH NUMBER])))

Column 3 = CALCULATE(SUM('NEW BUBA'[total]),ALLEXCEPT('NEW BUBA','NEW BUBA'[Date],'NEW BUBA'[period],'NEW BUBA'[Branch]))*'NEW BUBA'[Column 2] - it does allocation good accordingly to coefficients, only total amount is wrong, as it amount of actual+budget and only of March

The goal is to allocate amounts of BU COMM to other BU based on coefficient from related table (

Column 2 = RELATED(BU_KEY[%])) and accordingly to business area (total weight of each BA is 100%) see how it should be on example excel

Could you please advise what to do with formula?

Thank you in advance and best regards, 

Victoria

[v-easonf-msft · Accepted solution]

Hi， @PowerEti 

You need to enter a table like :

Then you can try formula like:

Alloc % = RELATED('Alloc Table'[Alloc%])

Admin = RELATED('Alloc Table'[ADMIN])

Alloc by date = CALCULATE(SUM( 'Table'[Amount]),ALLEXCEPT('Table','Table'[Date],'Table'[Account],'Alloc Table'[ADMIN]))*'Table'[Alloc %]+0

Alloc by month = 
 var a=CALCULATE(SUM( 'Table'[Amount]),ALLEXCEPT('Table','Table'[Month],'Table'[Account],'Alloc Table'[ADMIN]))*'Table'[Alloc %]+0
 return IF(DAY('Table'[Date])=1,a,BLANK())

result:

Best Regards,
Community Support Team _ Eason