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-
Step 1: Total Fees Per Day (Calculated Table or Measure)
You likely already have a column like TransactionDate and a FeeAmount.
Let’s define a measure to calculate total fees per day:
TotalFeesPerDay :=
SUMMARIZE(
Transactions,
Transactions[TransactionDate],
"TotalFee", SUM(Transactions[FeeAmount])
)
But since you need to identify the max day, you want a measure instead:
Step 2: Get Day with Highest Total Fee (Final DAX Measure)
Here’s the optimized DAX measure:
DayWithHighestTotalFee :=
VAR SummaryTable =
SUMMARIZE(
Transactions,
Transactions[TransactionDate],
"DailyTotalFee", SUM(Transactions[FeeAmount])
)
VAR MaxRow =
TOPN(
1,
SummaryTable,
[DailyTotalFee], DESC
)
RETURN
SELECTCOLUMNS(
MaxRow,
"Date", Transactions[TransactionDate],
"TotalFee", [DailyTotalFee]
)
This returns a single-row table with the date and fee.
If you want two separate measures (e.g., one for the date and one for the amount), you can split them:
MaxTotalFeeAmount :=
MAXX(
SUMMARIZE(
Transactions,
Transactions[TransactionDate],
"DailyFee", SUM(Transactions[FeeAmount])
),
[DailyFee]
)
DateWithMaxFee :=
CALCULATE(
MAX(Transactions[TransactionDate]),
FILTER(
ADDCOLUMNS(
VALUES(Transactions[TransactionDate]),
"DailyFee", CALCULATE(SUM(Transactions[FeeAmount]))
),
[DailyFee] = [MaxTotalFeeAmount]
)
)