cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

How to bring a result from other row on same column. Relative data reference

Hey guys, I'm a newb on Power BI. I'm stuked in a problem almost three days, so I've decided to share it cause I did not find nothing about.

 

There is a column what has a set of products ID, and a second one what maybe bring the raw material of this product. I want use a kind of relative references in a measure to bring cost of each product, but some products need to use this raw material as reference and some column ID. Someone could help with this problem?

 

Measure that I'm using to calculate the cost:

P. Cost d-30 =
var d-30 = (LASTDATE(Entry of product'[DateEntry]))-29

return
CALCULATE(sum(Entry of product'[Value Entry])/SUM(Entry of product'[Quantity Entry);Entry of product'[DateEntry]>=d30)

 

 

Eg:

 

Id ProductRaw Material expected result from the measure "P.Cost d-30"
657657100
658657100
659657100
920 250
400 300
700400300
1020 90
2000 90
3000 500
30013000500

 

 

 

How can I improve it to reach my goal?

 

Thx in advance!

 

"EDITED: My data tables and they relations with others tables already works perfectly, this measure mentioned above already works well with products without reference to raw material"

 

 

 

 

 

 

 

 

 

 

 

5 REPLIES 5
Resolver I
Resolver I

Hi,

 

Where´s value_entry, quantity_entry and date_entry fields?

Hi @Thyago_Rezende thx for reply, sorry forget mention this part.They are in others tables, the table of cost are conected by ID Product to table register of products mentioned below as a EG, date cost products to a table just with dates to be use as a slicer, my measure works well on produtcs without raw material reference, my problem is exclusive with the relative reference of raw material.

 

Best regards

Did you try to use LookUpValue function? Do you know it?

 

https://msdn.microsoft.com/en-us/library/gg492170.aspx

 

How I've tried it:

 

Raw material cost = LOOKUPVALUE(table [id product];table [raw material];[P. cost d-30])

 

The result comes blank, or It doesnt worked, or I misundertand how to use it.

(I dont have a column with the expected result , its come from a measure)

 

 

 

Hey guys, I'm reposting because my last post has bugged. I'm stuked on a problem almost three days, so I've decided to share it cause I did not find nothing about.

 

 

I'll try be most clear as possible.. .

 

There is a column what has a set of products ID, and a second one what maybe bring the raw material of this product. I want use a priority reference in measure the "raw material" and after ID product, a kind of relative references in a measure to bring cost of each product. All my tables are connected by ID product, so its ever bring ID Product first when I use a measure. Someone could help with this problem?

 

 

Eg:

 

Id ProductRaw Material expected result from the measure "P.Cost d-30"
657657100
658657100
659657100
920 250
400 300
700400300
1020 90
2000 90
3000 500
30013000500

 

 

 

How can achive this?

 

Thx in advance!

 

 

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors