Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
On first reading I don't think I get everything but I'm almost sure you're using the wrong terminology and that is a sure way to put people off (hence no replies for such a long time). It seems you're not after any kind of measure but calculated columns. The description of what you want to achieve is not clear at all. I would have a suggestion for you. Instead of pouring so much text into the post, you could certainly do with one good simple example of what it is you're after. Trust me: If you do it this way, you'll get solutions in no time.
If in doubt, please read THIS.
Thanks.
Thanks for the advice @Anonymous I am very new to building Power BI reports and have had only minimal training the rest I am trying to work out myself. and Yes, i am indeed probably using incorrect terminology for sure, I will think about how I need to pose my question to get my desired outcome.
Learning Power BI and especially DAX is a journey. Rather a longer one 🙂 But if I could learn it, so can you. You just have to persist and be willing to absorb from the best (think: Marco Russo and Alberto Ferrari, check out www.sqlbi.com). www.sqlbi.com is the ultimate source of truth about DAX.