Forum Discussion
Create a new column with DAX
Hi all , I am stuck with an issue for quiet sometime and unable to figure it out .
I am tryinng to create a new column by totally using four columns from my table using DAX .
So the need is.
If a PersonID is alloccated to a single ProjectID multiple times on the same month then I want the Average of those percentages reflected on the new column(NewPctAlloc) grouped by the Month.
| PersonID | ProjectID | Month | PercentageAllocated |
| 1 | a1 | Jan | 50 |
| 1 | a1 | Jan | 50 |
| 1 | a1 | Jan | 50 |
| 1 | a1 | Feb | 70 |
| 1 | a1 | Feb | 100 |
If a PersonID is alloccated to multiple ProjectID's in a same month then I want the sum of those percentages reflected on the new column(NewPctAlloc) grouped by the Month.
| PersonID | ProjectID | Month | PercentageAllocated |
| 2 | b2 | Jan | 30 |
| 2 | b3 | Jan | 50 |
| 2 | b4 | Jan | 50 |
| 2 | b2 | Feb | 30 |
| 2 | b3 | Feb | 50 |
| 2 | b2 | March | 20 |
| 2 | b3 | March | 20 |
| 2 | b4 | March | 30 |
| 2 | b5 | March | 30 |
The Final Output should look somewhat like this.
| PersonID | ProjectID | Month | PercentageAllocated | NewPctAlloc |
| 1 | a1 | Jan | 50 | 50 |
| 1 | a1 | Jan | 50 | 50 |
| 1 | a1 | Jan | 50 | 50 |
| 1 | a1 | Feb | 70 | 85 |
| 1 | a1 | Feb | 100 | 85 |
| 2 | b2 | Jan | 30 | 130 |
| 2 | b3 | Jan | 50 | 130 |
| 2 | b4 | Jan | 50 | 130 |
| 2 | b2 | Feb | 30 | 80 |
| 2 | b3 | Feb | 50 | 80 |
| 2 | b2 | March | 20 | 100 |
| 2 | b3 | March | 20 | 100 |
| 2 | b4 | March | 30 | 100 |
| 2 | b5 | March | 30 | 100 |
I will be really thankfull if there is a solution for this.
Thank you in advance .
-Martin
NewColumn = VAR _tbl = FILTER ( 'Sample', 'Sample'[PersonID] = EARLIER ( 'Sample'[PersonID] ) && 'Sample'[Month] = EARLIER ( 'Sample'[Month] ) ) RETURN IF ( COUNTROWS ( SUMMARIZE ( _tbl, 'Sample'[ProjectID] ) ) > 1, SUMX ( _tbl, 'Sample'[PercentageAllocated] ), AVERAGEX ( _tbl, 'Sample'[PercentageAllocated] ) )NewColumn = VAR _tbl = FILTER ( 'Sample', 'Sample'[PersonID] = EARLIER ( 'Sample'[PersonID] ) && 'Sample'[Month] = EARLIER ( 'Sample'[Month] ) ) VAR _Projects = SUMMARIZE ( _tbl, 'Sample'[ProjectID] ) RETURN IF ( COUNTROWS ( _Projects ) > 1, SUMX ( _Projects, AVERAGEX(FILTER(_tbl,'Sample'[ProjectID]=EARLIER('Sample'[ProjectID])),'Sample'[PercentageAllocated] )), AVERAGEX ( _tbl, 'Sample'[PercentageAllocated] ) )
5 Replies
NewColumn = VAR _tbl = FILTER ( 'Sample', 'Sample'[PersonID] = EARLIER ( 'Sample'[PersonID] ) && 'Sample'[Month] = EARLIER ( 'Sample'[Month] ) ) RETURN IF ( COUNTROWS ( SUMMARIZE ( _tbl, 'Sample'[ProjectID] ) ) > 1, SUMX ( _tbl, 'Sample'[PercentageAllocated] ), AVERAGEX ( _tbl, 'Sample'[PercentageAllocated] ) )Works perfectly fine .
Accepted as the solution..!!
Thanks wdx223_Daniel . 😀NewColumn = VAR _tbl = FILTER ( 'Sample', 'Sample'[PersonID] = EARLIER ( 'Sample'[PersonID] ) && 'Sample'[Month] = EARLIER ( 'Sample'[Month] ) ) VAR _Projects = SUMMARIZE ( _tbl, 'Sample'[ProjectID] ) RETURN IF ( COUNTROWS ( _Projects ) > 1, SUMX ( _Projects, AVERAGEX(FILTER(_tbl,'Sample'[ProjectID]=EARLIER('Sample'[ProjectID])),'Sample'[PercentageAllocated] )), AVERAGEX ( _tbl, 'Sample'[PercentageAllocated] ) )
Hi wdx223_Daniel . Thank you very much.
Just Wanted to make a change . In your above dax .RETURN IF ( COUNTROWS ( SUMMARIZE ( _tbl, 'Sample'[ProjectID] ) ) > 1, SUMX ( _tbl, 'Sample'[PercentageAllocated] ), AVERAGEX ( _tbl, 'Sample'[PercentageAllocated] ) )The ResultIfFalse is perfect.
But in the ResultIfTrue , i actually wanted the average of PercentageAllocated Based on the number of projects and then sum those averages in that particular month, instead of directly summing up the values.
For example.PersonID ProjectID MOnth %Allocated New%Allocated 1 a1 Jan 50 150 1 a1 Jan 50 150 1 a1 Jan 50 150 1 b1 Jan 100 150 1 b1 Jan 100 150 1 b1 Jan 100 150 The Change Is:
In your previous calculation the New%Allocated would have been 450 on all the rows. But it should have actually been as 150.
I really hope you could help me out in this wdx223_Daniel ...!!
Lot of Thanks.
-Martin
