get a MAX of a group and still be able to filter?

[ovonel]

I have  a Fact Revenue, and I am trying to get the MAX of a group…

I want to group  revenue by Customer and Sector… and then pick the MAX for each Customer…

(I can do this visually when I put in a Matrix, Customer, Sector and Revenue).

The below measures do the work for me

SubTotalCustomerSec =

CALCULATE ( SUM(FactRevenue[Revenue]), ALLEXCEPT ( FactRevenue, 'Customer'[Customer], 'Sector'[Sector] ) )

MaxSecerCustomer =

MAXX ( ALLEXCEPT ( FactRevenue, 'Customer'[Customer] ), [SubTotalCustomerSec] )

The problem is that I can’t filter… I want to achieve the above but still be able to select a specific Customer or pick a specific value for a higher level than customer…

If I select any specific Customer or ParentCustomer and add my above measures, the matrix brings all the rows for all ParentCustomers...

[ovonel]

I have  a Fact Revenue, and I am trying to get the MAX of a group…

I want to group  revenue by Customer and Sector… and then pick the MAX for each Customer…

(I can do this visually when I put in a Matrix, Customer, Sector and Revenue).

The below measures do the work for me

SubTotalCustomerSec =

CALCULATE ( SUM(FactRevenue[Revenue]), ALLEXCEPT ( FactRevenue, 'Customer'[Customer], 'Sector'[Sector] ) )

MaxSecerCustomer =

MAXX ( ALLEXCEPT ( FactRevenue, 'Customer'[Customer] ), [SubTotalCustomerSec] )

The problem is that I can’t filter… I want to achieve the above but still be able to select a specific Customer or pick a specific value for a higher level than customer…

If I select any specific Customer or ParentCustomer and add my above measures, the matrix brings all the rows for all ParentCustomers...

[mahoneypat · Accepted solution]

My bad on copying from your expressions incorrectly.  I meant to write this.

Max Measure =
VAR summary =
    ADDCOLUMNS (
        SUMMARIZE ( FactRevenue, 'Customer'[Customer], Sector[Sector] ),
        "cTotal", CALCULATE ( SUM ( FactRevenue[Revenue] ) )
    )
RETURN
    MAXX ( summary, [cTotal] )

Pat

[mahoneypat]

You could try an approach like this.

Max Measure =
VAR summary =
    ADDCOLUMNS (
        SUMMARIZE ( FactRevenue, 'Customer'[Customer], [SubTotalCustomerSec] ),
        "cTotal", CALCULATE ( SUM ( FactRevenue[Revenue] ) )
    )
RETURN
    MAXX ( summary, [cTotal] )

Pat

[ovonel]

@mahoneypat thanks for the ansewr but I can't write         SUMMARIZE ( FactRevenue, 'Customer'[Customer], [SubTotalCustomerSec] ),

since SUMMARIZE doesn't accept the measure.