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...

[Anonymous · Accepted solution]

[Total Revenue] = SUM( FactRevenue[Revenue] )

[Max By Cust & Sect] =
MAXX(
    SUMMARIZE(
        -- Fact tables must be hidden and no slicing
        -- by their columns should be performed.
        -- Dimensions are the correct entities to
        -- slice by.
        FactRevenue,
        -- CustomerID needs to be the unique
        -- identifier of Customer
        Customer[CustomerID],
        Customer[Sector]
    ),
    [Total Revenue]
)

ALLEXCEPT is a tricky function that should only be used if you fully understand what it does. Otherwise, you'll almost always get it wrong. See the description: ALLEXCEPT – DAX Guide

I'd also kindly suggest you read this: Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT - SQLBI