highest total fee

[setayesh-]

We have a payment assistance system and we charge a fee for each transaction. The fee amount varies depending on the user and the transaction amount. Now, I want to calculate the total fees received for each day, and then find the day with the highest total fee along with the amount. However, every query I try gives me the highest fee for a single transaction, not the day with the highest total.

[v-karpurapud]

Hi @setayesh- 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-karpurapud]

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!

[v-karpurapud]

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

[MarkLaf · Accepted solution]

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
    )
)

[Amar_Kumar]

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]

    )

)

[anilelmastasi]

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. ✅