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

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 329 members 3,851 guests
Please welcome our newest community members: