Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors