Allocating Cost Formula

Good day The table below shows a project # and in certain cases multiple locations per project. The cost for each project is charged to "Central" Location as shown in the example below. I would like...

Need Help

ryan_mayu

5 years ago

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#            ]))))

Icey

5 years ago

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.

Allocating Cost Formula.pbix27 KB

Jihwan_Kim

Super User

5 years ago

Hi, dw700d

Please try the below DAX measure.

Cost evenly allocate =
IF (
CALCULATE (
COUNT ( 'Table'[Project#] ),
ALLEXCEPT ( 'Table', 'Table'[Project#] )
) > 1,
IF (
SELECTEDVALUE ( 'Table'[Location] ) = "Central",
BLANK (),
DIVIDE (
CALCULATE ( SUM ( 'Table'[Cost] ), ALLEXCEPT ( 'Table', 'Table'[Project#] ) ),
CALCULATE (
COUNT ( 'Table'[Project#] ),
ALLEXCEPT ( 'Table', 'Table'[Project#] )
) - 1
)
),
CALCULATE ( SUM ( 'Table'[Cost] ), ALLEXCEPT ( 'Table', 'Table'[Project#] ) )
)