Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Cost Allocation

Just starting out and am running into a wall.  need some help with my calcuation. I was able to determine a filtered cost and wish to allocate it in accordance to % of revenue (another table). The departments are all part of a heirarchy as well as locations. Here is my desired result:

 
 
 

Source Tables (3) look like this:

 

 

Need Help
  • v-yingjl's avatar
    v-yingjl
    6 years ago

    Hi Anonymous ,

    You can create this measure:

    Allocated = 
IF (
    SELECTEDVALUE ( 'Table'[Departments] ) = "Dept 100",
    SUM ( 'Table'[Expense] )
        - SUM ( 'Table'[Expense] )
            * CALCULATE (
                SUM ( 'Table'[% of Rev] ),
                ALLEXCEPT ( 'Table', 'Table'[Location] )
            ),
    SUMX (
        FILTER ( 'Table', 'Table'[Departments] <> "Dept 100" ),
        'Table'[Expense]
    )
        + MINX ( ALLEXCEPT ( 'Table', 'Table'[Location] ), 'Table'[Expense] )
            * SUM ( 'Table'[% of Rev] )
)

    Attached the sample file in the below, hopes to help you.

     

    Best Regards,
    Yingjie Li

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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous 

     

    Not clear. What is actually required.

    Can you please explain more. Please understand that who wants to help are not aware about all other underlying data.

    post it with I/p and o/p records and also created measures, columns.

     

    If I understand correct with the limited info.

    Filter the both tables on 'Filter on this visual' section and select allocate for one and unallocated for other.

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , No very clear

    Try these


    expense = sum(Table[expense])
    % of total = divide(sum(Table[expense]),calculate(sum(Table[expense]), all(Table[department])))

     

    Can you share sample data and sample output in table format?

  • v-yingjl's avatar
    v-yingjl
    Community Support
    6 years ago

    Hi Anonymous ,

    You can create this measure:

    Allocated = 
IF (
    SELECTEDVALUE ( 'Table'[Departments] ) = "Dept 100",
    SUM ( 'Table'[Expense] )
        - SUM ( 'Table'[Expense] )
            * CALCULATE (
                SUM ( 'Table'[% of Rev] ),
                ALLEXCEPT ( 'Table', 'Table'[Location] )
            ),
    SUMX (
        FILTER ( 'Table', 'Table'[Departments] <> "Dept 100" ),
        'Table'[Expense]
    )
        + MINX ( ALLEXCEPT ( 'Table', 'Table'[Location] ), 'Table'[Expense] )
            * SUM ( 'Table'[% of Rev] )
)

    Attached the sample file in the below, hopes to help you.

     

    Best Regards,
    Yingjie Li

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