cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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!).

 

1.jpg

 

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

 

2.jpg

 

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

 

3.jpg

 

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

 

4.jpg

 

 

visually, it looks like this

 

 

5.jpg

 

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)

 

6.jpg

 

in the model, I created a calculated column 

 

price:= [rur] / [qty]

 

and a measure that gives the necessary solution

 

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

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

1 REPLY 1
Highlighted
Moderator v-yuezhe-msft
Moderator

Re: speed optimization of the running total function

@edvardoss

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

Regards,
Lydia

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.