Measure to calculate max of summarized table

[martti]

I've hit a mind block figuring out a measure. I need it to extract the maximum revenues per month per salesman. I have an input table such as this:

Date	Salesman	Revenue
01.11.2019	A	800
23.11.2019	A	200
03.12.2019	A	300
05.11.2019	B	400
03.12.2019	B	500

The result should be 1000 for November and 500 for December (for all salesmen), since these are the maximum revenues per salesman for the relevant months. I cannot summarize the input table by salesman and date in query editor, since the actual input data is 10000+ rows with a lot more columns, and is compiled of multiple other data sources. I tried to summarize the input table in the measure itself with SUMMARIZE, but the formula 

CALCULATE(
    MAXX(
        SUMMARIZE(
            'Table' ;
            'Table'[Salesman] ;
            'Date_Table'[Dates].[Month] ;
            'Table'[Revenue]
        ) ;
        SUM('Table'[Revenue])
    ) ;
    ALL('Table'[Salesman])
)

seems to give the total revenue per month. The ALL('Table'[Salesman]) filter is needed, since the result has to be the same for all salesmen (the end result is a graph with x-axis salesmen, y-axis revenues, filtered for months).

[Greg_Deckler]

Perhaps try this:

Measure = 
    MAXX(
        SUMMARIZE(
            ALL('Table') ;
            'Table'[Salesman] ;
            'Date_Table'[Dates].[Month] ;
            'Table'[Revenue]
        ) ;
        SUM('Table'[Revenue])
    ) ;

[martti]

For some reason, this still gives the revenue generated by the specific salesman when in reality it should be the maximum over all salesmen. I did try different positions in the formula for CALCULATE( , ALL('Table'[Salesman])), but it did not work when wrapping MAXX(), SUMMARIZE or the table itself in it. 

[parry2k]

@martti in date table add a month column and then use this measure

Measure = 
MAXX ( 
SUMMARIZE( 'Table', DateTable[Month], 'Table'[Salesman] ), 
CALCULATE( MAX ( 'Table'[Revenue] ) ) 
)

[martti]

This somehow gives the maximum of a specific revenue per client, e.g. final result from my example is November - 800 for salesman A, 400 for salesman B; December - 300 for salesman A, 500 for salesman B.

[Mariusz · Accepted solution]

Hi @martti 

Try this

test = 
VAR __tbl = 
GROUPBY(
    CALCULATETABLE( 'Table', ALL( 'Table' ), VALUES( Date_Table[Year Month] ) ) ,
    'Table'[Salesman],
    'Date_Table'[Year Month],
    "@sum", SUMX( CURRENTGROUP(), 'Table'[Revenue] ) 
)
RETURN 
MAXX( __tbl, [@sum] )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[jfishbac]

This works! BUT...It doesn't seem to respond to changes in filters that I apply.

My business has a series of business units (i.e. "BU") where I'd like to see the respective maximum for. So, in my underlying data, I have BU stored in another table. I'd like this m_MaxOrdersInYr to change based on the LOB I'm filtered on.

The code below works...how can I get it to respond dynamically when I filter on individual BUs?

m_MaxOrdersInYr = 
VAR _SummaryTable = 
GROUPBY(
    CALCULATETABLE('OSGM Data_LC',ALL('OSGM Data_LC'),VALUES('OSGM Data_LC'[r_YearOfPlan])),
    'OSGM Data_LC'[r_YearOfPlan],
    'OSGM Data_LC'[r_LOB],
    'OSGM Data_LC'[SBR Version],
    "@sum",SUMX(CURRENTGROUP(),'OSGM Data_LC'[ORDER_USD_d])
    )
RETURN
MAXX(_SummaryTable,[@sum])

[martti]

Yes, this works perfectly! Thank you!