Forum Discussion

setayesh-'s avatar
setayesh-
New Member
1 year ago
Solved

highest total fee

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 ...
  • MarkLaf's avatar
    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

    AmountDate
    106.8110/19/2024
    101.29/29/2024
    114.361/17/2024
    26.847/15/2024
    17.998/25/2024
    258.978/27/2024
    160.074/17/2024
    25.356/13/2024
    64.952/11/2024
    229.433/1/2024
    232.9411/29/2024
    30.1510/1/2024
    151.414/16/2024
    259.581/18/2024
    190.7112/1/2024
    86.7711/20/2024
    20.948/13/2024
    148.3210/5/2024
    71.592/27/2024
    1415/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
        )
    )