Cost Allocation/ Distribution

[Anonymous]

Hi everyone. I have 2 tables like this:

The cost will be distributed based on the ratio in table Allocation.

After distributed, the cost of MKT and IT will be expected to be 0.

Please help me with PBI. Thank you so much. 

[Anonymous · Accepted solution]

Hi  @Anonymous ,

Here are the steps you can follow：

1. In Power Query -- Add Column – Index Column – From 1.

2. Create calculated column.

Flag =
VAR _C4 = [Cost]
VAR _C3 =
    SUMX (
        FILTER (
            ALL ( 'Dept_Tabel' ),
            'Dept_Tabel'[Index]
                = EARLIER ( 'Dept_Tabel'[Index] ) - 1
        ),
        [Cost]
    )
VAR _G3 =
    MAXX (
        FILTER (
            ALL ( Allocation_Table ),
            'Dept_Tabel'[Dept] = EARLIER ( 'Dept_Tabel'[Dept] )
                && 'Allocation_Table'[Allocate] = EARLIER ( 'Dept_Tabel'[Dept] )
        ),
        [%]
    )
VAR _G4 =
    MINX (
        FILTER (
            ALL ( Allocation_Table ),
            'Dept_Tabel'[Dept] = EARLIER ( 'Dept_Tabel'[Dept] )
                && 'Allocation_Table'[Allocate] IN SELECTCOLUMNS ( 'Dept_Tabel', "1", [Dept] )
        ),
        [%]
    )
VAR _C5 =
    IF (
        'Dept_Tabel'[Dept] = "Sale",
        SUMX (
            FILTER (
                ALL ( 'Dept_Tabel' ),
                'Dept_Tabel'[Index]
                    = EARLIER ( 'Dept_Tabel'[Index] ) + 1
            ),
            [Cost]
        ),
        SUMX (
            FILTER (
                ALL ( 'Dept_Tabel' ),
                'Dept_Tabel'[Index]
                    = EARLIER ( 'Dept_Tabel'[Index] ) - 1
            ),
            [Cost]
        )
    )
VAR _G5 =
    MINX (
        FILTER (
            ALL ( Allocation_Table ),
            'Dept_Tabel'[Dept] = EARLIER ( 'Dept_Tabel'[Dept] )
                && 'Allocation_Table'[Allocate] = EARLIER ( 'Dept_Tabel'[Dept] )
        ),
        [%]
    )
VAR _G6 =
    MAXX (
        FILTER (
            ALL ( Allocation_Table ),
            'Dept_Tabel'[Dept] = EARLIER ( 'Dept_Tabel'[Dept] )
                && 'Allocation_Table'[Allocate] = EARLIER ( 'Dept_Tabel'[Dept] )
        ),
        [%]
    )
RETURN
    IF (
        'Dept_Tabel'[Dept] IN SELECTCOLUMNS ( 'Allocation_Table', "1", [Dept] ),
        0,
        IF (
            [Dept] = "Sale",
            _C4 + _C3 * _G3 + ( _C3 * _G4 + _C5 ) * _G5,
            _C4 + ( _C5 * _G4 + _C5 ) * _G6
        )
    )

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Kishore_KVN]

Hello @Anonymous , please let us know if they become '0' based on any calculation. If not tell us the logic you are looking into bit clearly. 

[Anonymous]

Hi @Kishore_KVN  thank you for checking on it.

For MKT and IT, just dont mind it because I'll set it fixly as 0.

For the other depts, I've noted down the formula to cal it right next to the result. It will be:

orginal cost  + cost that distributed from IT and MKT.

Thank you so much.