Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Need help created a new column with formula

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 easi...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    Hi Anonymous ,

     

    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] )

     

Nathaniel_C's avatar
Nathaniel_C
Icon for Community Champion rankCommunity Champion
6 years ago

Hi Anonymous 

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's avatar
Nathaniel_C
Icon for Community Champion rankCommunity Champion
6 years ago

Hi Anonymous ,

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's avatar
    Nathaniel_C
    Icon for Community Champion rankCommunity Champion
    6 years ago

    use "and"

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Where does Index come from?

      • Nathaniel_C's avatar
        Nathaniel_C
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Anonymous ,

         

        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