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 ) )
Hello setayesh- ,
To solve this correctly in Power BI (or any SQL/DAX-based environment), you need to aggregate the total fees per day first, and then find the day with the maximum total. Here’s how you can do this in DAX, assuming you have a table called Transactions with at least the following columns:
Transactions[TransactionDate]
Transactions[Fee]
This calculates total fees per day but doesn’t yet tell you which day is the maximum:
TotalFeesPerDay =
SUMX(
VALUES(Transactions[TransactionDate]),
CALCULATE(SUM(Transactions[Fee]))
)
Create a calculated table for daily totals:
DailyFeeSummary =
SUMMARIZE(
Transactions,
Transactions[TransactionDate],
"TotalFee", SUM(Transactions[Fee])
)
Create a measure to find the max fee day
MaxFeeDayAmount =
MAXX(DailyFeeSummary, [TotalFee])
IF you want to create a measure to get the date of that maximum fee:
MaxFeeDay =
CALCULATE(
MAX(Transactions[TransactionDate]),
FILTER(
DailyFeeSummary,
[TotalFee] = [MaxFeeDayAmount]
)
)
If this solved your issue, please mark it as the accepted solution. ✅