cancel
Showing results for
Did you mean:
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
Solution Sage

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.

Thanks.

New Member

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.

Solution Sage

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.

Announcements

#### Welcome to the User Group Public Preview

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

#### Microsoft Business Applications Summit sessions

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

#### Claim Your Badge & Digital Swag!

Check out how to claim yours today!

#### Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors
Top Kudoed Authors