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 ) )
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
ToTable
Dates (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
)
)