Cost Allocation

[Anonymous]

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:

[v-yingjl · Accepted solution]

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.

[amitchandak]

@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?

[pranit828]

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.