highest total fee

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

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]

    )

)

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

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!

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.