Max value from two columns - Show blanks

[merlingsf]

Hi,

I am having trouble geting a single max value for a row from three columns in a matrix.

The columns are: Name, Date(Month), Price

The Dataset:

Name	Month	Price
A	January	75.000
A	January	150.000
A	March	250.000
B	January	350.000
B	January	175.000
B	March	150.000
C	March	75.000
C	March	65.000
C	January	125.000

A simple Max() Formula will result in:

Name	January	March	Total (Max)
A	150.000	250.000	250.000
B	350.000	150.000	350.000
C	125.000	75.000	125.000
Total (Max)	350.000	250.000

What I am aiming for:

Name	January	March	Total (Max)
A		250.000	250.000
B	350.000		350.000
C
Total (Max)	350.000	250.000

So, it should show a blank when the the Max value is not valid in this context. C does not have the max price in either January or March hence blank.

Any suggestions?

[v-lid-msft · Accepted solution]

Hi @merlingsf ,

We can try to create a measure to meet your requirement:

Measure =
SUMX (
    DISTINCT ( 'Table'[Month] ),
    VAR result =
        MAXX (
            CALCULATETABLE ( DISTINCT ( 'Table'[Name] ), ALLSELECTED () ),
            CALCULATE ( MAX ( 'Table'[Price] ) )
        )
    RETURN
        IF ( CALCULATE ( MAX ( 'Table'[Price] ) ) = result, result, BLANK () )
)

By the way, PBIX file as attached.

Best regards,

[merlingsf]

Hi @v-lid-msft,

Thank you so much for taking the time to look at this.

This seems to solve the problem.