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

Anonymous — Wed, 23 Jun 2021 11:20:55 GMT

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

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

Anonymous — Wed, 23 Jun 2021 15:23:13 GMT

[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

topic Re: get a MAX of a group and still be able to filter? in DAX Commands and Tips

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

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