Allocate a cost center to other cost centers

[pickup18]

Hi all,

I want to allocate a common cost center to other business unit cost centers through my Common Allocation Look-up table. As you can see, cost centers 1 and 2 are common and are transfered to other business units like shirt and dress (there are really 2 cost centers pertaining to dress). For example, 1 is allocated shirt and dress based on a percentage in the common allocation look-up. I want to turn cost centers 1 and 2 balances' to 0 and transfer them to shirt and dress' total for cost center 1, and to hat and shoes' total for 2 per fiscal year.

Hence the resulting total of each business unit will be for 2122

Leather common = 0

Shoes = 60 (50 from common, 10 from its own cost center)

Hat = 60 (50 from common, 10 from its own cost center)

for 2223

leather common = 0

Shoes = 70 (60 from common, 10 from total of its own cost center/s)

Hat = 50 (40 from common, 10 from total of its own cost center/s)

In this way, when I create a stacked bar chart that shows total cost of each business unit by fiscal year, each business unit will already reflect its true total (since common has been allocated)

PowerBI file (with data uploaded and modeled) 

excel file of data 

Thank you so much!

[danextian]

Hi @pickup18 

Please try this:

Allocation = 
VAR TBL =
    SUMMARIZE (
        'Operational costs',
        'Cost Center Look up'[Business unit],
        'Operational costs'[Fy]
    )
VAR Result =
    SUMX (
        ADDCOLUMNS (
            TBL,
            "@Allocation",
                VAR __FY = [Fy] -- assign 'Operational costs'[Fy] to a variable to avoid ambiguity with 'Cost allocation look-up'[FY] due to having the same column name
                VAR __ALLOC =
                    CALCULATE (
                        SUM ( 'Cost allocation look-up'[Allocation] ),
                        FILTER (
                            'Cost allocation look-up',
                            'Cost allocation look-up'[FY] = __FY
                                && 'Cost allocation look-up'[To business unit] = [Business unit]
                        )
                    )
                RETURN
                    IF (
                        [Business unit] IN { "Dress", "Shirt" },
                        __ALLOC * [StitchingCommon],
                        IF ( [Business unit] IN { "Hat", "Shoes" }, __ALLOC * [LeatherCommon] )
                    )
        ),
        [@Allocation]
    )
RETURN
    Result
        + CALCULATE (
            SUM ( 'Operational costs'[Amount] ),
            FILTER (
                VALUES ( 'Cost Center Look up'[Business unit] ),
                NOT ( CONTAINSSTRING ( 'Cost Center Look up'[Business unit], "Common" ) )
            )
        )

 The result is different from your sample.  For 2223, Hat should be at .60 *100 +10 which is 70 but yours is 50. The same goes for Shoes.

Please see sample pbix for the details.

[pickup18]

subreHi, Thank you so much for this. Although on my end it is not allocating the common costs but it is subtracting the common from the total. I'm still figuring out why, I saw the PBI file and it's definitely working on your file. My guess it is returning 0 from this part:

RETURN

                    IF (

                        [Business unit] IN { "Dress", "Shirt" },

                        __ALLOC * [StitchingCommon],

                        IF ( [Business unit] IN { "Hat", "Shoes" }, __ALLOC * [LeatherCommon] )

                    )

[danextian]

I forgot to mention that I removed the relationships between 'Cost allocation look-up' and 'Cost Center Look up'. That's possbily the cause.

[pickup18]

Sorry for flooding you with replies. I now found the difference. Yours do not have blanks under the common columns, mine have as the common column is only showing values for stitching common. Would you know why yours are returning the allocation amount for all business units while mine is not?

[pickup18]

Hi @danextian,

Thanks for the patience. I already did this. Looked further and what's happening is that when I return either __ALLOC or [StitchingCommon] on its own, it is resulting to the right values but when i multiply them together it is returning a blank value. Do you know why this is happening?

Here's how I tested it:

Test Allocation Logic = 
VAR __FY = SELECTEDVALUE('Operational costs'[Fy])
VAR __BusinessUnit = SELECTEDVALUE('Cost Center Look up'[Business unit])
VAR __ALLOC =
    CALCULATE (
        SUM ( 'Cost allocation look-up'[Allocation] ),
        FILTER (
            'Cost allocation look-up',
            VALUE('Cost allocation look-up'[FY]) = VALUE(__FY)
                && 'Cost allocation look-up'[To business unit] = __BusinessUnit
        )
    )
VAR ConvertedStitchingCommon = VALUE([StitchingCommon])
VAR AllocationLogic =
    IF (
        __BusinessUnit IN { "Dress", "Shirt" },
        __ALLOC * ConvertedStitchingCommon,
        IF ( __BusinessUnit IN { "Hat", "Shoes" }, __ALLOC * [LeatherCommon] )
    )
RETURN
    AllocationLogic

allocation Logic is not returning anything, but __ALLOC or LeatherCommon by its own is returning the correct value 

[danextian]

Hi @pickup18 

Testing your formula and comparing the result with the allocation without adding its own value, the result looks fine to me.

[pickup18]

Hi @danextian ! Looks like your code is really woking. 

However, please see my reply above. Your table returns constant value for the Common columns regardless of the business unit while mine returns the total only for that specific common (so 0 for dress, but 100 for stitching common)  might be the reason why __ALLOC * Common returns a blank value. Would you know why? 😊

[danextian]

You mentioned that VAR AllocationLogic is not returning anything so  i tested that on your sample data but it seems to be working. Test Allocation Logic in my screenshot is your own formula.

[pickup18]

Yes, but in my original data the leather common, for example, is not returning 100 when business unit is not leather common. However, in your matrix table it is returning 100 all through out.

The rows under my Stitching Column measure are blank for those that are no Stitching Common.  My matrix table is returning something like this:

Haha sorry been figuring out why it's not working when yours is perfectly fine and this is the only difference i found 😊

[danextian]

What happens if you return just VALUE([StitchingCommon])? Im suspecting it has something to do with it being used in a variables. Variables are immutable and evaluated once.

[pickup18]

Hi @danextian  here's my code, would you what may be causing non allocation of costs? As for you question, It is returning the total for the Common 

Allocation = 
VAR TBL =
    SUMMARIZE (
        Cost,
        Cost[BU],
        Cost[fy]
    )
VAR Result = -- Calculates the total allocation
    SUMX (
        ADDCOLUMNS (
            TBL,
            "@Allocation",
                VAR __FY = [fy] -- 'Operational costs'[Fy] to a variable to avoid ambiguity with  'Cost allocation look-up'[FY] due to having the same column name
                VAR __ALLOC =
                    CALCULATE (
                        SUM ( 'Commons Allocation Look-up'[Allocation]),
                        FILTER (
                            'Commons Allocation Look-up',
                            VALUE('Commons Allocation Look-up'[FY]) = VALUE(__FY)
                                && 'Commons Allocation Look-up'[To BU]= [BU]
                        )
                    )
                RETURN
                    IF (
                        [BU] IN { "DL", "ST" },
                        CALCULATE(__ALLOC * [Total Clothing Common PHP], FILTER (
                            'Commons Allocation Look-up',
                            VALUE('Commons Allocation Look-up'[FY]) = VALUE(__FY)
                                && 'Commons Allocation Look-up'[To BU]= [BU]
                        )),
                        IF ( [BU] IN { "FE", "DI" }, CALCULATE(__ALLOC * [Total Shoe Common PHP], FILTER( 'Commons Allocation Look-up',
                            VALUE('Commons Allocation Look-up'[FY]) = VALUE(__FY)
                                && 'Commons Allocation Look-up'[To BU]= [BU]
                        ))
                    )
        )),
        [@Allocation]
    )
RETURN
    Result
        + CALCULATE (
            [Total Cost in PHP],
            FILTER (
                VALUES ( 'COST'[BU]),
                NOT ( CONTAINSSTRING ( 'Cost'[BU], "Common" ) )
            )
        )

[danextian]

Honestly, I'm at a loss now. You are using a different data and your formula is different from what I initially provided.

[pickup18]

It is returning the total for the Common 🙂