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

     

v-frfei-msft's avatar
v-frfei-msft
Icon for Community Support rankCommunity Support
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] )

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

    Anonymous ,

     

    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.