Need help created a new column with formula

[kman42]

I have a table called Team Members with columns called prop_id, Allocation, and Role. Unfortunately, Allocation is not always correct in the data source for some situations and it can't be fixed easily there, so I need to use a formula to resolve it during the query. Basically, if there are multiple Team Member records with Role='lead' for a given prop_id, then the Allocation is correct, but if there is only one Team Member record with Role='lead' then Allocation comes in as 0. I need to transform that 0 to 100 for these cases. I want to create a new column called "Corrected Allocation", but I'm not really sure how to write the M code to accomplish this.

I think I need to use code to create a temporary table with all records with the current row's prop_id and Role='lead', then evaluate if there is only one row in that table. If so, Corrected Allocation would be set to 100. If there is more than one row, then Corrected Allocation would be set to Allocation.

Can someone help me translate that to M?

Thanks!

[v-frfei-msft · Accepted solution]

Hi @kman42 ,

We can create a calculated column as below.

Column = 
VAR a =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            'Table',
            'Table'[Role] = "Lead"
                && 'Table'[prop_id] = EARLIER ( 'Table'[prop_id] )
                && 'Table'[Member ID] <= EARLIER ( 'Table'[Member ID] )
        )
    )
VAR b =
    CALCULATE (
        SUM ( 'Table'[Allocation] ),
        FILTER (
            'Table',
            'Table'[Role] = "Lead"
                && 'Table'[prop_id] = EARLIER ( 'Table'[prop_id] )
                && 'Table'[Member ID] <= EARLIER ( 'Table'[Member ID] )
        )
    )
RETURN
    IF ( NOT ( ISBLANK ( a ) ) && b = 0, 100, 'Table'[Allocation] )

[kman42]

This totally worked. I was trying to do it with M in Power Query, but just creating a custom column with a DAX formula was easy with your instructions.

[Nathaniel_C]

@kman42 ,

One thing to be aware of is that there is significantly higher overhead using a Calculated Column as opposed to doing this in Power Query.

[Nathaniel_C]

Hi @kman42 

I need to transform that 0 to 100 for these cases. I want to create a new column called "Corrected Allocation", but I'm not really sure how to write the M code to accomplish this.

How about using a conditional statement  and change Index to Allocation.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[Nathaniel_C]

Hi @kman42 ,

Then add [Role] =lead

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[Nathaniel_C]

use "and"

[kman42]

Where does Index come from?

[Nathaniel_C]

@kman42 ,

In that post, read "How about using a conditional statement  and change Index to Allocation. That was just the column name in the pbix I am working in. You put your own column name in there. When it shows you the if statement, right after [Allocation] is where to put the and along with [Role] = "lead".

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[kman42]

So there is one additional level of complexity that I don't think that quite addresses. Here is what I want the table to look like:

prop_id         Member ID       Role             Allocation             Corrected Allocation
1                     A         lead              0                           100
2                     B         lead              50                         50
2                     C         lead              50                         50
3                     D         admin           0                           0
3                     E         lead              0                            100
4                     F         lead              33                          33
4                     G         lead              33                           33
4                     H         lead              33                           33
5                     I         lead              100                         100
5                     J         lead              0                           0

For prop_id #1 there is only one team member and she is lead, so her allocation should change from 0 to 100. prop_id #2 has two people in the lead role, so their allocation is already correct and it just has to be copied over directly. prop_id #3 only has one person in the lead role, so their allocation should be corrected from 0 to 100. prop_id # 4 has three people in the lead role so their allocations are also correct. prop_id # 5 has two leads, so even though one is 0, it is correct and shouldn't be changed. Note that allocations are arbitrary, not evenly split, but they do add up to 100.

So it isn't as simple as looking at just another column in the same row. I need to somehow look across multiple rows. That said, the logic is fairly simple, I think. 

[Nathaniel_C]

Hi @kman42 ,

So the logic is count the number of rows of prop id, and if 1 and if role = lead, then change to 100 else just use the existing allocation. Correct?

Have to go off line for a bit, but tell me if you can write this, else I will do when I am back.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[kman42]

Count the number of rows of prop_id where role='lead'. If it = 1, then set Corrected Allocation to 100. Else, Corrected Allocation = Allocation.

[Nathaniel_C]

Hi @kman42 ,

Does Allocation come in at 0 for any other situations?

Nathaniel

[kman42]

Yes, when Role has other values, Allocation may equal zero. I only care about the Team Members with Role='lead'. If Role='admin', Allocation should be zero, but they get filtered out of my report anyway.