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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It 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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Fabric Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.