Allocating Cost Formula

dw700d 

is this waht you want?

Column = if('Table'[Location]="Central",'Table'[Cost],maxx(FILTER('Table','Table'[Project#            ]=EARLIER('Table'[Project#            ])&&'Table'[Location]="Central"),'Table'[Cost]/CALCULATE(COUNTROWS('Table'),'Table'[Location]<>"Central",ALLEXCEPT('Table','Table'[Project#            ]))))

Hi dw700d ,

What ryan_mayu  created is a custom column using M language in Power Query Editor.

If you wnat to create a column or measure using DAX, try this:

Column =
VAR Cost_ =
    CALCULATE ( SUM ( 'Table'[Cost] ), ALLEXCEPT ( 'Table', 'Table'[Project#] ) )
VAR LocationCount =
    CALCULATE (
        COUNT ( 'Table'[Location] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Project#] ),
            'Table'[Location] <> "Central"
        )
    )
RETURN
    IF ( 'Table'[Location] = "Central", [Cost], DIVIDE ( Cost_, LocationCount ) )

Measure =
VAR Cost_ =
    CALCULATE ( SUM ( 'Table'[Cost] ), ALLEXCEPT ( 'Table', 'Table'[Project#] ) )
VAR LocationCount =
    CALCULATE (
        COUNT ( 'Table'[Location] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Project#] ),
            'Table'[Location] <> "Central"
        )
    )
RETURN
    IF ( MAX( 'Table'[Location] ) = "Central", Cost_, DIVIDE ( Cost_, LocationCount ) )

Best Regards,

Icey

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