Forum Discussion
highest total fee
- 1 year ago
This is relatively straightforward to do with just measures and INDEX.
First, quick overview of my test data and model. Will share the M/DAX for generating these at bottom of post if interested.
I generated 10k rows of test data, Sales, in Power Query similar to below (all dates in 2024):
Sales
Amount Date 106.81 10/19/2024 101.2 9/29/2024 114.36 1/17/2024 26.84 7/15/2024 17.99 8/25/2024 258.97 8/27/2024 160.07 4/17/2024 25.35 6/13/2024 64.95 2/11/2024 229.43 3/1/2024 232.94 11/29/2024 30.15 10/1/2024 151.41 4/16/2024 259.58 1/18/2024 190.71 12/1/2024 86.77 11/20/2024 20.94 8/13/2024 148.32 10/5/2024 71.59 2/27/2024 141 5/22/2024 I made a calculated table, Dates, marking as a date table and relating to Sales.
So, my model looks like:
It sounds like you want to display the 'Top Date' by SUM( Sales[Amount] ) and also display said amount, something equivalent to: SUM( Sales[Amount] ) where Date = 'Top Date'.
Here are measures to achieve this:
Top Date = VAR _topDate = INDEX( 1, SUMMARIZECOLUMNS( Dates[Date], "SalesOfDay", CALCULATE( SUM( Sales[Amount] ) ) ), ORDERBY( [SalesOfDay], DESC ) ) RETURN CALCULATE( VALUES( Dates[Date] ), _topDate )Top Amount = VAR _topDate = INDEX( 1, SUMMARIZECOLUMNS( Dates[Date], "SalesOfDay", CALCULATE( SUM( Sales[Amount] ) ) ), ORDERBY( [SalesOfDay], DESC ) ) RETURN CALCULATE( SUM( Sales[Amount] ) , _topDate )As you can see, the initial calculation of the 'Top Date' is handled the same for each measure using INDEX.
To showcase how these work. Here is a quick gif of the measures in cards, along with Dates[Month] slicing and a regular sorted table to validate the top values from the measures.
Code for generating tables if interested:
Sales (Power Query)
let Source = List.Generate( ()=>0, each _ < 10000, each _ + 1, each { Number.Round( Number.RandomBetween( 10, 300 ), 2 ), Date.From( Number.RoundDown( Number.RandomBetween( Int64.From( #date(2024,1,1) ), Int64.From( #date(2024,12,31) ) + 0.99999 ) ) ) } ), ToTable = Table.FromRows( Source, type table [ Amount = Currency.Type, Date = date ] ) in ToTableDates (DAX)
Dates = GENERATE( CALENDARAUTO(), VAR _dt = [Date] VAR _yr = YEAR( _dt ) VAR _qr = QUARTER( _dt ) VAR _moNo = MONTH( _dt ) VAR _mo = FORMAT( _dt, "mmm" ) RETURN ROW( "Year",_yr, "Quarter",_qr, "Month No",_moNo, "Month",_mo ) )
Hi setayesh-
We have not received a response from you regarding the query and were following up to check if you have found a resolution from the information provided below. If you find the response helpful, please mark it as the accepted solution and provide kudos, as this will help other members with similar queries.
Thank You!