Hi There
Sorry for the long windedness of this question- I really need your help
I currently have a table called ‘Purchase Order Line’ that lists all of my purchase orders detailing.
[PO Number]
[Order Date]
[Expected delivery date]
[Order status]
[Vendor]
[Itemcode]
[Order qty]
[Receipted Qty] = SUM('Purchase Order Line'[ORDER QTY]) - SUM('Purchase Order Line'[QTY OUTSTANDING])
[Qty outstanding]
[Unit price]
[RECEITPED VALUE] = SUM('Purchase Order Line'[PURCHPRICE]) * [RECEIPTED QTY]
It is filtered by slicers for [packing group ID] and [expected delivery month]
I use a specific product in the Packing group and the price we pay for it from a specific vendor each month as a benchmark against all the other variants of this product purchased in the category.
This is determined using the following
BENCHMARK PRICE = CALCULATE(SUM('Purchase Order Line'[PURCHPRICE]), 'Vendors'[VENDORCODE] IN {"123456789"},'Purchase Order Line'[ITEMCODE] IN {"50001447"},Products[PACKAGINGGROUPID] IN {"SCR"}
This works beautifully so far.
And gives me the result I want
I then want to compare my benchmark price for the month against all the other prices paid for each variant ,you would think it is as easy as creating the measure:
[VARIANCE]=SUM(‘Purchase Order Line’[BENCHMARK PRICE]- ‘Purchase Order Line’[PURCHPRICE])
However When I create this measure in the Purchase Order line table it only does the calculation for cells in that column that meet the [benchmarkprice]’s filter criteria and all other cells in the column are blank .
I have also tried
[VARIANCE]=(‘Purchase Order Line’[BENCHMARK PRICE]- ‘Purchase Order Line’[PURCHPRICE])
This gives a similar result except all other cells show a negative version of the purchase price (e.g. if purch price was $5 the result shown is -$5)
My intent is to take the [VARIANCE] and multiply it by the [receipted qty] to show me the value we would have paid if we had purchased that product at benchmark price this would be called [VARIANCE VALUE] using a measure like:
[VARIANCE VALUE]=SUM(‘Purchase Order Line’[VARIANCE] * ‘Purchase Order Line’[RECEITPED QTY])
Then Subtract [RECEITPED VALUE] from [VARIANCE VALUE] to give me an indication as to whether I made a profit or loss against the benchmark. With a measure like:
[SAVING]=SUM([RECEITPED VALUE] - [VARIANCE VALUE]
Any help you can give would be greatly appreciated.
