07-29-2018 08:10 AM
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 ( 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
07-30-2018 12:57 AM