Forum Discussion

fosterXO's avatar
fosterXO
Frequent Visitor
3 years ago
Solved

Measure case based on other column text values

Hi,

I have a big model in PowerBI where there are many different aggregation and grouping based on columns being displayed or not on the final table.

 

Simplifying: I need to do a conditional statement doing the sum if the value of column 1 is A1 but doing the MAX() if the value of column 1 is A2.

 

 

I need to have that information in the same colum of the final output. How would you go for this one? 

Thank you very much for your help!

  • tamerj1's avatar
    tamerj1
    3 years ago

    fosterXO 

    To obtain correct total you can may try 

    Measur =
SUMX (
    VALUES ( 'Table'[Column1] ),
    CALCULATE (
        IF (
            'Table'[Column1] = "A",
            SUM ( 'Table'[Column2] ),
            MAX ( 'Table'[Column2] )
        )
    )
)
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi fosterXO ,

    Please have a try.

    Create a measure.

    Measure =
SWITCH (
    TRUE (),
    MAX ( 'Table'[Column1] ) = "A1",
        SUMX (
            FILTER (
                ALL ( 'Table' ),
                'Table'[Column1] = SELECTEDVALUE ( 'Table'[Column1] )
            ),
            'Table'[Column2]
        ),
    MAX ( 'Table'[Column1] ) = "A2",
        MAXX (
            FILTER (
                ALL ( 'Table' ),
                'Table'[Column1] = SELECTEDVALUE ( 'Table'[Column1] )
            ),
            'Table'[Column2]
        ),
    BLANK ()
)

     

    If I have misunderstood your meaing, please provide more details.

     

    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.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi

     

    If(selectedvalue(Column1)="A1",sum(column2),Max (Column2))

    if you have more choice of column1 values make the if statement each time

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    fosterXO 

    To obtain correct total you can may try 

    Measur =
SUMX (
    VALUES ( 'Table'[Column1] ),
    CALCULATE (
        IF (
            'Table'[Column1] = "A",
            SUM ( 'Table'[Column2] ),
            MAX ( 'Table'[Column2] )
        )
    )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi fosterXO ,

    Please have a try.

    Create a measure.

    Measure =
SWITCH (
    TRUE (),
    MAX ( 'Table'[Column1] ) = "A1",
        SUMX (
            FILTER (
                ALL ( 'Table' ),
                'Table'[Column1] = SELECTEDVALUE ( 'Table'[Column1] )
            ),
            'Table'[Column2]
        ),
    MAX ( 'Table'[Column1] ) = "A2",
        MAXX (
            FILTER (
                ALL ( 'Table' ),
                'Table'[Column1] = SELECTEDVALUE ( 'Table'[Column1] )
            ),
            'Table'[Column2]
        ),
    BLANK ()
)

     

    If I have misunderstood your meaing, please provide more details.

     

    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.

  • AnkitKukreja's avatar
    AnkitKukreja
    Super User
    3 years ago

    Hi fosterXO 

     

    I agree with Anonymous . But the result would be in a separate column. Power BI would not let you show this data in the same column.