Cost Allocation/ Distribution

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