Allocating Cost Formula

[dw700d]

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 to create a formula that evenly allocates the cost charged at the central location to the states within each project. For example in project C 150k will be evenly allocated to MA & KY.  Can anyone help?

Project#	Location	Cost
A	Central	200,000.00
A	NY	0
B	Central	300,000.00
B	LA	0
C	Central	300,000.00
C	MA	0
C	KY	0
D	Central	150,000.00
E	Central	10,000.00
E	WY	0
E	IL	0
E	CA	0

[ryan_mayu · Accepted solution]

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

[Jihwan_Kim]

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

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[dw700d]

@Jihwan_Kim 

Thank you for the repsonse. The numbers are not allocating correctly. Any other suggestions?

[Jihwan_Kim]

Hi,

If it is OK with you, can I see your sample pbix file that you used the measure for the above?

[ryan_mayu · Accepted solution]

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

[dw700d]

@ryan_mayu 

Thanks Ryan I cant get past the if statement. It doesnt recognize my table or columns. Could there be something missing from the measure?

[Icey · Accepted solution]

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.

[ryan_mayu]

@dw700d 

are you creating a measure or a column? my solution is for a new column, not a measure. You want a solution for a measure?