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?