Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
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
Hi xiaoxin, thanks for replying.
I realised the results you attain and the results I aim to get are different as seen in the above.
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
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:
Total Value = SUMX(VALUES(Symbol[Symbol]),CALCULATE(SUM(Trx[Quantity]),DATESBETWEEN(DimDate[DateKey],BLANK(),MAX(DimDate[DateKey])),ALL(DimDate))*CALCULATE(VALUES(Price[Px]),LASTNONBLANK(DATESBETWEEN(DimDate[DateKey],DATEADD(DimDate[DateKey],-30,DAY),MAX(DimDate[DateKey])),CALCULATE(COUNT(Price[Px])))))
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."
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.
Hi CahabaData,
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,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |