Forum Discussion

MartinAa's avatar
MartinAa
Frequent Visitor
3 years ago
Solved

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 ...
Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    3 years ago
    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's avatar
    wdx223_Daniel
    3 years ago
    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's avatar
wdx223_Daniel
Community Champion
3 years ago
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's avatar
    MartinAa
    Frequent Visitor
    3 years ago

    Works perfectly fine . 

    Accepted as the solution..!! 
    Thanks wdx223_Daniel  . ğŸ˜€ 

    • wdx223_Daniel's avatar
      wdx223_Daniel
      Community Champion
      3 years ago
      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's avatar
        MartinAa
        Frequent Visitor
        3 years ago

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

  • MartinAa's avatar
    MartinAa
    Frequent Visitor
    3 years ago

    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