Showing results for 
Search instead for 
Did you mean: 
edvardoss Occasional Visitor
Occasional Visitor

speed optimization of the running total function

There is a table with sales in units (qty) and revenue (rur) by products (SKU) and stores. The product in the table is aggregated into brands (this is a very simple example) .
the goal is to calculate for each cell of the PivotTable in the context of brands, extract the calculated price for 50% of the unit sales boundary in cumulative total (It's not a simple median for price!).




The rows in the example I deliberately ordered so that the estimated price (revenue "rur" divided by units "qty") was ordered in ascending order




Further, the units sales ordered by price are considered to be a cumulative total and are converted to a percentage of the total amount




After that, we look for the maximum percentage of shipments no more than the required border (50%) and return the price to the cell of the summary table for it





visually, it looks like this





and the result in the table looks like this (in fact, we should not count 50% and the boundaries of 10% and 90% on a much larger table, but I'm simplifying here)




in the model, I created a calculated column 


price:= [rur] / [qty]


and a measure that gives the necessary solution


    MAXX (
        FILTER (
            ADDCOLUMNS (
                ADDCOLUMNS (
                    "cs.qty", SUMX ( FILTER ( 'tbl', tbl[price] <= EARLIER ( tbl[price] ) ), tbl[qty] )
                "cs.qty.%", [cs.qty] / SUM ( tbl[qty] )
            [cs.qty.%] <= 0.5

it works on a small table of 1 cell but once the table of 20 brands is built for 10 months (200 cells) the result is impossible to wait. I know that EARLIER isn't optimal but I don't know how to optimize my function

Moderator v-yuezhe-msft

Re: speed optimization of the running total function


About PowerPivot  issues,  please post questions in PowerPivot forum to get dedicated support.


Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 54 members 941 guests
Please welcome our newest community members: