cancel
Showing results for
Did you mean:
Helper II

## Cross reference multiplication based on date and stock price

Dear all,

I have the following table:

What measure shall I use to achieve the highligted red figure. I need a measure/formula which would allow me to filter by client type, stock name as well as the date.

Warmest regards,

6 REPLIES 6
Solution Sage

## Re: Cross reference multiplication based on date and stock price

I would trim out the unneeded columns in the upper sample, and then do a pivot.

Having said that; it seems viable if there is really a limited number of Stocks as per your sample.  If the reality is that there is an infinite variety of stocks then I wouldn't take this approach of a left-to-right style display and instead stay with the normal up/down table of data and go with grouping around the dates to sum.

Helper II

## Re: Cross reference multiplication based on date and stock price

Thank you for replying. As I am trying to construct a dashboard I would love to do it in PowerBI. I just need to get to results as shown in the red highlighted column - it can be a new column or new measure. Shall I join some tables together? I am open to whatever method to get the results except that it must be done in PowerBI.

Thank you,

Community Support

## Re: Cross reference multiplication based on date and stock price

Hi TeeTreeThree,

I agree with CahabaData’s point of view, you could refer to below sample:

`Dax: Stock Total = ADDCOLUMNS(ALL('Stock Record'),"Price",LOOKUPVALUE('Stock Detail'[Stock Price],'Stock Detail'[Date],'Stock Record'[Date],'Stock Detail'[Stock Name],'Stock Record'[Stock Name]))`

Calculate coulmns of value and current quality:

`Current Quality = SUMX(FILTER(all('Stock Total'),AND('Stock Total'[Date]<=EARLIER('Stock Total'[Date]) , 'Stock Total'[Stock Name]=EARLIER('Stock Total'[Stock Name]))),'Stock Total'[Quality])`

`value = ABS( 'Stock Total'[Quality] * 'Stock Total'[Price])`

About total value, you could take a look at below formulas:

1. Measure.

`TotalByDate = CALCULATE(SUM('Stock Total'[value]), Filter (ALL( 'Stock Total'), SUMX(FILTER('Stock Total', 'Stock Total'[Date] = Earlier ('Stock Total'[Date] ) ),'Stock Total'[value])))`

2. Calculate column.(it seems simple than measure)

`TotalValueByDate = SUMX( FILTER(ALL('Stock Total'),'Stock Total'[Date]=EARLIER('Stock Total'[Date])),'Stock Total'[value])`

>>I need a measure/formula which would allow me to filter by client type, stock name as well as the date.

You could add the new condition to formula:

`TotalByDate = CALCULATE(SUM('Stock Total'[value]), Filter (ALL( 'Stock Total'), SUMX(FILTER('Stock Total', AND('Stock Total'[Date] = Earlier ('Stock Total'[Date] , 'Stock Total'[TrxType]=”Sell”) ) ),'Stock Total'[value])))`

In addition, I think use a slicer is more suitable to filter data.

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
Helper II

## Re: Cross reference multiplication based on date and stock price

I realised the results you attain and the results I aim to get are different as seen in the above.

Community Support

## Re: Cross reference multiplication based on date and stock price

Hi TeeTreeThree,

>>I realised the results you attain and the results I aim to get are different as seen in the above.

Could you provide some detail about these?

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
Helper II

## Re: Cross reference multiplication based on date and stock price

Hi Xiaoxin,

I managed to find the solution to this by reading some of the post:

The relationship I built as follow:

The measure used to attain my desired results:

However:

I managed to attain what I want in this simplified example however when apply to actual data this error show

"A table of multiple values was supplied where a single value was expected."

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors