Measure case based on other column text values

[fosterXO]

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 · Accepted solution]

@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 · Accepted solution]

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.

[tamerj1 · Accepted solution]

@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] )
        )
    )
)

[AnkitKukreja]

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.

[Anonymous]

Hi

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

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