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, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |
Super User

Hi,

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

Highlighted
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, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 211 members 2,900 guests
Recent signins: