Divide Cost About Departmentents

[Norbertus]

Hi

how can i make the next calculation?

We need to devide cost to another department.

In my example we want to devide the cost on:

• department 7010 to Department 7020 for 100% (column AssignTo => 100% | 7020)
• department 7030 to Department 7040 for 40% and 7050 for 60% (column AssignTo => 40% | 7040 | 60% | 7050)

the column "AssignTo" is build like " % | department | % | department | % | department" etc

The result we needed is:

Can somebody help me with this chalenge? 

With kind regards, Norbertus

[Barthel · Accepted solution]

Hey @Norbertus,

Processing a division in a cell is difficult to process in DAX. It may be more convenient to process the distribution in another table, split across several rows, rather than processed in one cell. So for example: 

FK DepartmentAllocation FK DepartmentPercentage

1	2	1
3	4	0,4
3	5	0,6

You can create the view you want in a table visual. Place the department from the dimension table into the visual table. Now we need the three measures. You could handle it this way:

InitialCost = 
SUM ( 'FK Department Amount'[Amount] )

AssignCost = 
VAR _in =
    SUMX (
        CALCULATETABLE (
            Allocation,
            TREATAS (
                VALUES ( 'Dim Department'[PK Department] ),
                Allocation[Allocation FK Department]
            )
        ),
        CALCULATE (
            [InitialCost],
            'Dim Department'[PK Department] = EARLIER ( Allocation[FK Department] ),
            REMOVEFILTERS ( 'Dim Department' )
        ) * Allocation[Percentage]
    )
VAR _out =
    SUMX (
        CALCULATETABLE (
            Allocation,
            TREATAS (
                VALUES ( 'Dim Department'[PK Department] ),
                Allocation[FK Department]
            )
        ),
        CALCULATE (
            [InitialCost] * -1,
            'Dim Department'[PK Department] = EARLIER ( Allocation[FK Department] ),
            REMOVEFILTERS ( 'Dim Department' )
        ) * Allocation[Percentage]
    )
VAR _result = _in + _out
RETURN
    _result

CostAfterDivide = 
[InitialCost] + [AssignCost]

AssignCost measure is now set up in such a way that it dynamically determines the result based on the allocation table. Should the allocation ever change in the future, you only need to adjust the allocation table, and the measure will automatically adjust accordingly.

Result:

[Norbertus]

Hi @Barthel 

That is a nice solution.
I try to make the new dimension table, that should work

After a small change in your formula it works 

Assign Total = 

VAR _in =
    SUMX(
        CALCULATETABLE (
            Allocation,
            TREATAS (
                VALUES('Dim Department'[PK Department]),
                Allocation[FK Department] 
            )
        ),
        CALCULATE(
            [InitialCost],
            'Dim Department'[PK Department] = EARLIER(Allocation[FK DepartmentAllocation]),
            REMOVEFILTERS('Dim Department')
        ) * Allocation[Percentage]
    )

VAR _out =
    SUMX(
        CALCULATETABLE(
            Allocation,
            TREATAS(
                VALUES('Dim Department'[PK Department]),
                Allocation[FK DepartmentAllocation] 
            )
        ),
        CALCULATE(
            [InitialCost] * -1,
            'Dim Department'[PK Department] = EARLIER(Allocation[FK DepartmentAllocation]),
            REMOVEFILTERS ('Dim Department')
        ) * Allocation[Percentage]
    )
VAR _result = _in  + _out
RETURN
    _result

thanks for your comprehensive reply