cancel
Showing results for
Did you mean:
Post Patron

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

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

1 ACCEPTED SOLUTION
Solution Sage
``````[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

Solution Sage
``````[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

Announcements

#### Launching new user group features

Learn how to create your own user groups today!