cancel
Showing results for
Did you mean:
Frequent Visitor

## Return price from one table and multiply by volume in another

Hi,

I've spent the last few hours on this and decided I need help. I'm quite new to Power BI so still feeling my way around, although an experienced in Excel.

I have multiple tables based in a SQL database/warehouse (using Direct Query).

One of which contains the standard price for a product (MBEWH[STPRS]), another table contains the transactions with the sold volume (VBRP[FKING]. The material number is [MATNR] in both tables and I want use the most recent Std Cost based on two columns - Year (MBEWH[LFGJA]) and Month (MBEWH[LFMON]).

I would like to calculate the cost of the total product sold but can't get the lookup correct. I've tried LOOKUPVALUE but can't get it to work. I also tried a FIRSTNONBLANK suggestion from another post but couldn't get it to work either.

I've hit a brick wall and not sure where to go next? Any assistance on a way to handle the lookup would be greatly appreciated.

5 REPLIES 5
Community Support Team

## Re: Return price from one table and multiply by volume in another

Hi @yacatac,

You can try to use below formula to find out most recent product price, then you can use it calculate out most recent cost of total sold:

```MR Std Cost =
VAR _currNumber =
SELECTEDVALUE ( MBEWH[MATNR] )
VAR _lastDate =
MAXX (
ADDCOLUMNS ( ALLSELECTED ( MBEWH ), "Merge", [LFGJA] * 100 + [LFMON] ),
[Merge]
)
RETURN
CALCULATE (
LASTNONBLANK ( MBEWH[STPRS], [STPRS] ),
FILTER (
ALLSELECTED ( MBEWH ),
MBEWH[MATNR] = _currNumber
&& [LFGJA] = LEFT ( _lastDate, 4 )
&& [LFMON] = RIGHT ( _lastDate, 2 )
)
)

Cost of the total product sold =
SELECTEDVALUE ( VBRP[FKING] ) * [MR Std Cost]```

If above not help, please share a pbix file with some of sample data and expected result, it will be help for test and coding formula.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
Super User IV

## Re: Return price from one table and multiply by volume in another

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

## Re: Return price from one table and multiply by volume in another

Something I forgot to add in the original post was the need to filter based on site  - MBEWH[BWKEY] & VBRP[VSTEL]

I hope this link provides enough detail

https://www.dropbox.com/s/9v0ux5e7bq7pu40/Delivered%20Margin_DirectQ%20Cost%20Help.pbix?dl=0

Unfortunately I had to Import the data to truncate the info due to confidentiality, however the original file is DirectQuery tables.

Regards

Stuart

Frequent Visitor

## Re: Return price from one table and multiply by volume in another

Bump

Community Support Team

## Re: Return price from one table and multiply by volume in another

HI @yacatac,

After play with your sample, I can't found some of fields which you mentioned.

For your sample data, I can only do summarize on tables and get unique records based on specific summarize mode.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!