Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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).
Solved! Go to 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] )
Perhaps try this:
Measure =
MAXX(
SUMMARIZE(
ALL('Table') ;
'Table'[Salesman] ;
'Date_Table'[Dates].[Month] ;
'Table'[Revenue]
) ;
SUM('Table'[Revenue])
) ;
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.
@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] ) )
)
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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.
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] )
Yes, this works perfectly! Thank you!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |