cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Steve1969
New Member

Filtered field skewing Measures

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.

 

 

3 REPLIES 3
daxer
Solution Sage
Solution Sage

@Steve1969 

 

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 @daxer  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.

@Steve1969 

 

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors