'Bucketing' sales in price matrix.

[francoisl]

Hi all, 

I need to bucket sales in a price matrix.   The price matrix has discounts upon unit sold.  I am currently ignoring date, product tables for the sake of clarity but if we need them please feel free to add them in the explanation.

Here`s a simple example.

Price Grid table with break (imported table)

Product	Level	FROM	TO	UNIT PRICE
A	1	0	10	10.00
A	2	11	20	8.50
A	3	21	999999	7.75
B	1	0	15	45.00
B	2	16	999999	37.00
D	1	0	999999	25.00

Sales Table with qty ordered (imported table) 

INVOICE	PRODUCT	QTY	UNIT PRICE
1	A	3	10.00
2	D	45	25.00
3	C	3	12.00
4	A	15	8.50
5	A	17	8.50
6	B	7	45.00
7	B	9	45.00

Desired Results

Product	Level	FROM	TO	UNIT PRICE	QTYSOLD
A	1	0	10	10.00	3
A	2	11	20	8.50	32
A	3	21	999999	7.75	0
B	1	0	15	45.00	16
B	2	16	999999	37.00	0
D	1	0	999999	25.00	45

My challenge here is that I do not want to use the price because there is always a good business reason to change the price on an order. Quantity should be the driver to bucket those sales.   Of course the level is not on the sales table... that would have been to easy. 

Product C is not in the price grid and should not appear in the desired result. If nothing was sold then we need to show 0.

999999 is always the upper value of the last level even if there is only one level.  

I am struggling with the lookupvalue or the firstnonblank on the sales table but I do not think this is the right way to do this.  I will try related dax command but currently any comments are welcome. 

Many thanks

François 

[Anonymous]

Hi @francoisl ,

You can create two measures as below:

QTYSOLD = 
CALCULATE (
    SUM ( 'Sales'[QTY] ),
    FILTER (
        'Sales',
        'Sales'[PRODUCT] = MAX ( 'Price'[Product] )
            && 'Sales'[UNIT PRICE] = MAX ( 'Price'[UNIT PRICE] )
    )
) + 0

Measure = 
SUMX (
    VALUES ( 'Price'[Product] ),
    SUMX ( VALUES ( 'Price'[Level] ), [QTYSOLD] )
)

Best Regards

Rena

[francoisl]

Hi all, 

I was specifically looking for a solution that uses an the interval and not the price. 

Thanks I will keep looking for a specific solution. 

F

[Anonymous · Accepted solution]

Hi @francoisl ,

You can update the measure "QTYSOLD " as below:

QTYSOLD =

CALCULATE (

SUM ( 'Sales'[QTY] ),

FILTER (

'Sales',

'Sales'[PRODUCT] = MAX ( 'Price'[Product] )

&& 'Sales'[QTY]>=MAX('Price'[FROM])

&&'Sales'[QTY]<=MAX('Price'[TO])

)

) + 0

Best Regards

Rena

[HotChilli]

Looks like the above solutions are pretty close.

Add a column to PriceGrid table like this :

ColQTYSOLD = CALCULATE(SUM(SalesTable[QTY]), FILTER(SalesTable, SalesTable[PRODUCT] = PriceGrid[PRODUCT]  && SalesTable[QTY]  >= PriceGrid[FROM] && SalesTable[QTY] <= PriceGrid[TO])) + 0

[amitchandak]

@francoisl , you can get a new column in price grid table like

QTYSOLD = sumx(filter(Sales, sales[PRODUCT]=PriceGrid[PRODUCT] &&sales[UNIT PRICE]=PriceGrid[UNIT PRICE] ), sales[QTY])

You can also use sales[UNIT PRICE]>=PriceGrid[from] from && sales[UNIT PRICE]< =PriceGrid[to]  in place of  sales[UNIT PRICE]=PriceGrid[UNIT PRICE]