Allocating a Column's value to a different Column

cmayers · November 29, 2021, 7:50pm

I have a report that I’m doing up and I’m having some issues with.
Basically, if there exists a value in Cost Center 0

I need to allocate that value to the other columns (e.g. 10, 11, 12, 13, 20 …):

By these percentage rates (Table is Default Cost Center Allocation):

And zero out that 0 Cost center column.

So using the row account 5040 under the Discounts category, you have a value of $3,371:

The math I need is for all cost centers but for example cost center 10 would be:
$1,392 (original number for cost center 10) + ($3,371 * 10%)

Then I would follow the same convention of allocation to each cost center (e.g. 10, 11, 12, 13, 20 …) and finally the cost center 0 would display empty as it has been allocated to other cost centers.

cmayers · November 30, 2021, 2:41pm

I was able to get the associated percentage rates where they need to be but I still need to figure out how to take the value within cost center 0 relative to the row and column and allocate that to each column.

LOOKUPVALUE(‘Cost Center Allocation’[Value], ‘Cost Center Allocation’[Cost Center], MAX(‘GL Transactions(gbkmut)’[COST CENTER]) )

BINavPete · November 30, 2021, 5:58pm

One way to approach this would be at the Power query stage by filtering off the 0 cost centres values from GL Transactions, merge with the cost centre allocation table. This will repeat each row in the GL Transaction table for however many allocations you have. Then with Add Column, perform the allocation and append this table back to your GL Transaction table.
It will mean that you have no 0 cost centres in the fact table, which is OK if you are not using the 0 cost centre numbers elsewhere in your model.

Working from where you are though I would write a new measure:

Curr with Allocations =
SWITCH(
    TRUE(),
    Max( GL Transactions( gbkmut ) [CostCentre] ) = 0, Blank(),
    [Currency] * LOOKUPVALUE(
        ‘ Cost Center Allocation ’ [Value],
        ‘ Cost Center Allocation ’ [Cost Center],
        MAX( ‘ GL Transactions( gbkmut ) ’ [COST CENTER] )
    )
)

Hope you get on OK
Pete

cmayers · December 1, 2021, 3:55pm

Thank you for the feedback; the issue I’m facing is that the value of that cost centre ( 0 ) needs to be allocated to those other columns.

So isolating the lookup gives the proper allocation percentages and isolating the currency function will give the correct dollar amount.

However, if exists an amount in cost center = 0 then allocate that in addition to what already exists in the context.

Definitely correct idea for percentages but rather than “in addition to” the formula above would multiply the context by the related percentage but not actually allocate any of the cost center = 0 column to the other columns.

I hope that made sense?
Thank you again for your assistance

BINavPete · December 1, 2021, 4:21pm

Sorry my fault,

Measure needs amending to

Curr with Allocations =
SWITCH(
    TRUE(),
    Max( GL Transactions( gbkmut ) [CostCentre] ) = 0,
    Blank(),
    [Currency] +(
        [Currency] * LOOKUPVALUE(
            ‘ Cost Center Allocation ’ [Value],
            ‘ Cost Center Allocation ’ [Cost Center],
            MAX( ‘ GL Transactions( gbkmut ) ’ [COST CENTER] )
        )
    )
)

cmayers · December 1, 2021, 4:31pm

Wouldn’t the context of [Currency] still be in the column it’s in?
So if there was a value in cost center 0, the EQ here would just look for the associated percentage value if it weren’t already being blanked out first?

I guess I’m not seeing how the value in cost center 0 is getting allocated to those other columns?

BINavPete · December 1, 2021, 4:47pm

You will need to change the measure in your matrix to [Curr with Allocations].
Give it a go and see if it has the desired results

Pete

cmayers · December 1, 2021, 5:09pm

Unfortunately not; it’s doing what I was worried about.

Maybe this will better explain my dilemna:

The math in excel:

BINavPete · December 1, 2021, 6:16pm

Understand Maths - been apportioning costs as an accountant for years. LOL.

I’m not quite there but it’s beer time in Norfolk England, sorry

I changed the data types in your default cost center allocation table Key - whole number and Value to %. They were both text.

In Measure table “01 Key Measures - Pete” I’ve made a start and got some numbers in there but I don’t think its quite right yet. You will understand your figs better than me. So if you can round it off let me know.

Pete
CostApportionement.pbix (2.5 MB)

cmayers · December 1, 2021, 6:40pm

Have a draught for me! Thanks for the help

cmayers · December 2, 2021, 9:09pm

Anyone else have any ideas?

EnterpriseDNA · December 6, 2021, 10:40pm

Hi @cmayers! We noticed that your inquiry was left unsolved for quite some time now.

Looks like your inquiry was out of the experts and users’ bounds.

We strive to answer ALL inquiries in the forum. However, if you are sure that you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, you can check out tutorials to learn this yourself as your membership also comes with relevant resources that may help you with your Power BI education.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!

cmayers · December 7, 2021, 3:13pm

I assume this is an automated response(?) but I’ve been working with PBI for a few years.
I don’t feel this is something I can “learn.”

Would love if someone else could take a crack at this; the first response (albeit still appreciated) wasn’t the right direction for this problem.