Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Create a new column using DAX

Hi!

 

I’m struggling to create a new column using DAX.

 

Here are the tables and their relations:

INDICATEUR[Clé] = INITIATIVE-LINKS-PARENT[Clé du parent]

INITIATIVE-LINKS-PARENT[Clé] = INITIATIVE[Clé]

 

I would like to create a new column in the INITIATIVE table with the following behaviour:

  • For each row, I would like to have: [Valeur d’affaires] / Sum of [Valeur d’affaires]
  • Sum of [Valeur d’affaires] have to be group by INDICATEUR[Clé]

 

Here are the assumptions:

  • Each INITIATIVE row is related to only one INDICATEUR row
  • An INDICATEUR row have one or more related INITIATIVE rows

 

I have tried several functions as CALCULATE and FILTER but It seems that I missed something.

 

Here’s an example:

 

Thanks for your help.

 

Best,

Damien

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

    Please have a try.

    Check the relationships between the tables.

    Then create two columns.

    Column =
CALCULATE (
    MAX ( 'INITIATIVE-LINKS-PARENT'[CIe du parent] ),
    FILTER (
        'INITIATIVE-LINKS-PARENT',
        'INITIATIVE-LINKS-PARENT'[Cie] = EARLIER ( INACTIVE[Cie] )
    )
)

    Column_2 =
VAR _hou =
    CALCULATE (
        SUM ( INACTIVE[Valeur d affaires] ),
        FILTER ( INACTIVE, INACTIVE[Column] = EARLIER ( INACTIVE[Column] ) )
    )
RETURN
    DIVIDE ( INACTIVE[Valeur d affaires], _hou )

    Or try to use measure.

    Measure =
VAR qian_ =
    MAX ( INACTIVE[Valeur d affaires] )
VAR hou_ =
    CALCULATE (
        SUM ( INACTIVE[Valeur d affaires] ),
        FILTER (
            ALL ( 'INITIATIVE-LINKS-PARENT' ),
            'INITIATIVE-LINKS-PARENT'[CIe du parent]
                = SELECTEDVALUE ( 'INITIATIVE-LINKS-PARENT'[CIe du parent] )
        )
    )
RETURN
    qian_ / hou_

    If I have misunderstood your meaning, please provide you pbix file without privacy information and your desired output.

    How to Get Your Question Answered Quickly 

     

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

    Please have a try.

    Check the relationships between the tables.

    Then create two columns.

    Column =
CALCULATE (
    MAX ( 'INITIATIVE-LINKS-PARENT'[CIe du parent] ),
    FILTER (
        'INITIATIVE-LINKS-PARENT',
        'INITIATIVE-LINKS-PARENT'[Cie] = EARLIER ( INACTIVE[Cie] )
    )
)

    Column_2 =
VAR _hou =
    CALCULATE (
        SUM ( INACTIVE[Valeur d affaires] ),
        FILTER ( INACTIVE, INACTIVE[Column] = EARLIER ( INACTIVE[Column] ) )
    )
RETURN
    DIVIDE ( INACTIVE[Valeur d affaires], _hou )

    Or try to use measure.

    Measure =
VAR qian_ =
    MAX ( INACTIVE[Valeur d affaires] )
VAR hou_ =
    CALCULATE (
        SUM ( INACTIVE[Valeur d affaires] ),
        FILTER (
            ALL ( 'INITIATIVE-LINKS-PARENT' ),
            'INITIATIVE-LINKS-PARENT'[CIe du parent]
                = SELECTEDVALUE ( 'INITIATIVE-LINKS-PARENT'[CIe du parent] )
        )
    )
RETURN
    qian_ / hou_

    If I have misunderstood your meaning, please provide you pbix file without privacy information and your desired output.

    How to Get Your Question Answered Quickly 

     

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.