cancel
Showing results for
Did you mean:
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

```   price.50%qty=
MAXX (
FILTER (
'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

Highlighted
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.

Announcements

#### Challenge: Can You Solve These?

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

#### Community News & Announcements

Get your latest community news and announcements.

#### 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.

#### Win Power BI Swag with Community Kudopalooza!

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

Top Kudoed Authors
Users Online
Currently online: 54 members 941 guests
Recent signins: