Forum Discussion
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 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 |
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.
7 Replies
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
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.
Thank you for the repsonse. The numbers are not allocating correctly. Any other suggestions?
Hi,
If it is OK with you, can I see your sample pbix file that you used the measure for the above?
