Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vgdits
Frequent Visitor

Multiplying measure between tables

Hi,

 

after several failed attempts or very unhandy solutions I would love to ask for advise.

I am trying to use PBI to revaluate stock.

Having product table with currency,

transactions table with product, qty, and date

exhc.rate table with currency, all dates within month until last available date within month (weekends will have no rates)

quote table with product, dates withing months until last available date (DIFFERENT from last exch. rate date - quotes can be either before or after last exch. rate)

 

I need to cumulate qty in stock, multiply the qty with rate from last date of the month and by quote from last date.

and in addition to this, SUM correctly within matrix table the stock value.

 

I am able to have measure to have correct qty cumul, incl. in periods where is no movement, but as soon as I make measure multiplying this cumul qty by rate and price returns correct row value but total line is wrong...

 

Any suggestions on which path I should take?

a) Need to use better measures

b) Running totals already in PowerQuery for each year_month and later measures

c) Everything in PowerQuery - merges...

 

Excel

 

Greetings from Liberec!

 

Sample

1 REPLY 1
v-juanli-msft
Community Support
Community Support

Hi @vgdits

The matrix in your screenshot is your expected result or actual result?

What have you done in your pbix, could show me a screenshot to know which columns or measures you add in "row","column" and "value" field of the matrix?

the YM column should show year/month from 2001/1 to 2001/12 (12 months), right?

 

Based on my testing, you may need to cretae a new table with power query or a new calculated table with DAX, since adding a new calculated table to the model may slow the performance, please let know how large is your real dataset so i can choose a better way to slove this problem.

 

Best Regards

Maggie

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.