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-
Could you please confirm if your query have been resolved the solution provided by MarkLaf , Amar_Kumar and anilelmastasi ? If they have, kindly mark the helpful response and accept it as the solution. This will assist other community members in resolving similar issues more efficiently.
Thank you