cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
MartinAa
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. 

PersonIDProjectIDMonthPercentageAllocated
1a1Jan50
1a1Jan50
1a1Jan50
1a1Feb70
1a1Feb100

 

 

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. 

PersonIDProjectIDMonthPercentageAllocated
2b2Jan30
2b3Jan50
2b4Jan50
2b2Feb30
2b3Feb50
2b2March20
2b3March20
2b4March30
2b5March30

 

 

The Final Output should look somewhat like this.

PersonIDProjectIDMonthPercentageAllocatedNewPctAlloc
1a1Jan5050
1a1Jan5050
1a1Jan5050
1a1Feb7085
1a1Feb100

85

2b2Jan30

130

2b3Jan50

130

2b4Jan50

130

2b2Feb30

80

2b3Feb50

80

2b2March20

100

2b3March20

100

2b4March30

100

2b5March30

100

 

I will be really thankfull if there is a solution for this. 

Thank you in advance . 

-Martin

2 ACCEPTED SOLUTIONS
wdx223_Daniel
Super User
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] )
    )

View solution in original post

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

View solution in original post

5 REPLIES 5
wdx223_Daniel
Super User
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] )
    )

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.

PersonIDProjectIDMOnth%AllocatedNew%Allocated
1a1Jan50150
1a1Jan50150
1a1Jan50150
1b1Jan100150
1b1Jan100150
1b1Jan100150

 

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

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

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

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors