Forum Discussion
Need help created a new column with formula
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] )
Community Championuse "and"
Where does Index come from?
- Nathaniel_C
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.
NathanielSo 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 0For 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 Anonymous ,
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