Forum Discussion

dw700d's avatar
dw700d
Post Patron
5 years ago
Solved

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's avatar
    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's avatar
    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.

ryan_mayu's avatar
ryan_mayu
Super User
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#            ]))))

  • dw700d's avatar
    dw700d
    Post Patron
    5 years ago

    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?

     

    • ryan_mayu's avatar
      ryan_mayu
      Super User
      5 years ago

      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?

    • Icey's avatar
      Icey
      Community Support
      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.