cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## 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.

-Martin

2 ACCEPTED SOLUTIONS
Super User
``````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] )
)``````
Super User
``````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 5
Super User
``````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] )
)``````
Frequent Visitor

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

Frequent Visitor

Works perfectly fine .

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

Super User
``````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] )
)``````
Frequent Visitor

@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  ..!!

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors