Forum Discussion

Redacted_VAR's avatar
Redacted_VAR
Helper I
2 years ago
Solved

Sum Rows Until Dynamic Value is Reached

Hi All,

 

trying to flesh out if this idea is possible, as i'm not even sure which functions to start with!

I've got a basic sales table for a single salesperson:

CompanyRevenue
Company A£1,252,445.32
Company B£984,544.44
Company C£650,433.00
Company D£321,433.55
Company E£25,120.44
Company F£19,432.00
Company G-£124,244.12

 

Total Revenue: £3,129,164.63

What i want to do is for each salesperson (who has a table as per the above) calculate 95% of their total revenue (in this case, £2,972,706.40) and then work out how many companies is needed until the sum of revenue meets or exceeds that number. so for example company A-D = £3.208,856 which exceeds that 95% number, so companies E,F & G would be moved on from that salesperson.

any suggestions of how i could acheive this would be greatly appreciated. 

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    2 years ago

    Redacted_VAR 

    maybe you can try this

    Column = sumx(FILTER('Table','Table'[Company]<=EARLIER('Table'[Company])),'Table'[Revenue])/sum('Table'[Revenue])
     
    Column 2 = if(ISBLANK(MINX(FILTER('Table','Table'[Column]<EARLIER('Table'[Column])&&'Table'[Column]>=0.95),'Table'[Column])),"y")
     
     
    then sum the column2="y"
     
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi,

    Thanks for the soution ryan_mayu  provided,it is excellent,  and i want to offer some more information for user to refer to.

    hello Redacted_VAR , you can try the following calculated column.

    Sum_revenus =
VAR _addcolumns =
    ADDCOLUMNS (
        'Table',
        "Cumm_sum",
            SUMX (
                FILTER ( 'Table', [Company] <= EARLIER ( 'Table'[Company] ) ),
                [Revenue]
            )
    )
VAR _addflag =
    ADDCOLUMNS (
        _addcolumns,
        "minsum",
            VAR totalrevenue =
                SUM ( [Revenue] ) * 0.95
            VAR min_cummsum =
                MINX ( FILTER ( _addcolumns, [Cumm_sum] >= totalrevenue ), [Company] )
            RETURN
                min_cummsum
    )
RETURN
    MINX (
        FILTER (
            _addflag,
            [Company] = EARLIER ( 'Table'[Company] )
                && [Company] <= [minsum]
        ),
        [Cumm_sum]
    )

    Output

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

2 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    2 years ago

    Redacted_VAR 

    maybe you can try this

    Column = sumx(FILTER('Table','Table'[Company]<=EARLIER('Table'[Company])),'Table'[Revenue])/sum('Table'[Revenue])
     
    Column 2 = if(ISBLANK(MINX(FILTER('Table','Table'[Column]<EARLIER('Table'[Column])&&'Table'[Column]>=0.95),'Table'[Column])),"y")
     
     
    then sum the column2="y"
     
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi,

    Thanks for the soution ryan_mayu  provided,it is excellent,  and i want to offer some more information for user to refer to.

    hello Redacted_VAR , you can try the following calculated column.

    Sum_revenus =
VAR _addcolumns =
    ADDCOLUMNS (
        'Table',
        "Cumm_sum",
            SUMX (
                FILTER ( 'Table', [Company] <= EARLIER ( 'Table'[Company] ) ),
                [Revenue]
            )
    )
VAR _addflag =
    ADDCOLUMNS (
        _addcolumns,
        "minsum",
            VAR totalrevenue =
                SUM ( [Revenue] ) * 0.95
            VAR min_cummsum =
                MINX ( FILTER ( _addcolumns, [Cumm_sum] >= totalrevenue ), [Company] )
            RETURN
                min_cummsum
    )
RETURN
    MINX (
        FILTER (
            _addflag,
            [Company] = EARLIER ( 'Table'[Company] )
                && [Company] <= [minsum]
        ),
        [Cumm_sum]
    )

    Output

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.