Create a new column with DAX

[MartinAa]

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

[wdx223_Daniel · Accepted solution]

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

[wdx223_Daniel · Accepted solution]

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

[MartinAa]

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

[MartinAa]

Works perfectly fine . 

Accepted as the solution..!! 
Thanks @wdx223_Daniel  . 😀 

[wdx223_Daniel · Accepted solution]

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

[MartinAa]

@wdx223_Daniel  - You Beauty ..!! 😃

You just taught me soo much with the above DAX... !!

Thank you soo much for the timely response.. Works perfect..!! 

Will over-ride the previous solution with the current one as the solution. Thanks @wdx223_Daniel  ..!!