Forum Discussion
Solved
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 ...
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] ) )
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] )
)
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