Return Max Number result by Project, and combine max figures when Payment Type is the same

[Wilbike]

Hi Everyone,

I am trying to create a measure that is able to return the max Payment Value for a Project (1,2 or 3), and when the payment type is the same for the project, I'd like this combined value to be included when determining the maximum payment value for a project.

For example:

Project 2 has two Y payment types which have a combined value 110. I'd like this value to be produced from the MAX measure as the maximum Payment Value for this Project

The previous measure I have tried is:

=SUMX(VALUES(PaymentTable[Project]),CALCULATE(MAX(PaymentTable[Payment Value])))

But this will only return the single maximum figure for payment value, and not the combined value if the Payment Type is the same for a given project.

If this can be done without a measure then I'd be interested in hearing this to! 

Thanks in advance!

[Wilbike]

Thanks for posting @johnt75 .

@daXtreme that solution worked! Thank you very much!

[johnt75]

You could create a measure like 

Max Value =
VAR SummaryTable =
    ADDCOLUMNS (
        SUMMARIZE ( 'Table', 'Table'[Payment type] ),
        "@val", CALCULATE ( SUM ( 'Table'[Value] ) )
    )
RETURN
    MAXX ( SummaryTable, [@val] )

[daXtreme · Accepted solution]

Here's the same measure as @johnt75's but without the unnecessary fluff:

Max Value =
MAXX(
    DISTINCT( 'Table'[Payment type] )
    CALCULATE( SUM( 'Table'[Value] ) )
)

and might be faster as it does not materialize results as the measure above does because of ADDCOLUMNS.