Sum Rows Until Dynamic Value is Reached

[Redacted_VAR]

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:

Company	Revenue
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. 

[ryan_mayu · Accepted solution]

@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"

[v-xinruzhu-msft · Accepted solution]

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.

[ryan_mayu · Accepted solution]

@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"