dax calculation total is incorrect

[lawada4]

im trying to calculate return on investement for stocks data based on the formula ( current price-initial cost)/initial cost. 

i calculated first the initial cost ( purchased amount * number of stocks purchased) then the current position price as:

(current price* number of stocks purchased) and finally i want to get Profit/Loss amount and Profit/Loss %.

simply i subtracted : current price-initial cost and for the percentage i calculated it as:( current price-initial cost)/initial cost.

  the result is correct for indivisual stock but the overall total purchased amount value, Profit/Loss value and percentage is incorrect 

 in the total , it's taking the sum(last closing price) * sum (number of stocks purchased): 53*135934=7204502 which is wrong. the correct result should be the sum of column 4693.579996 which is the sum of all values in the amount purchased column.

the line chart is also showing incorect percentages in the overall return but only correct values for indiviual stocks

the used DAX measures 

• amount purchased value = CALCULATE(SUM('user portfolio'[number of stocks purchased])*[last closing price])

• p&l = CALCULATE([amount purchased value]-SUM('user portfolio'[Amount Purchased]))
  

• p&l % = CALCULATE(([amount purchased value]-SUM('user portfolio'[Amount Purchased]))/SUM('user portfolio'[Amount Purchased]),FILTER('stocks data','stocks data'[date diff]>=0))
• puchasing date diff column = DATEDIFF(RELATED('user portfolio'[Date Purchased]),'stocks data'[Date],DAY)

any idea on how to fix the calculation to show the correct overall profit/loss value and percetage?

note: when calcuting ROI there are other things taken into consideration like : dividend, commission fees, etc. and this example is just a simple case 🙂

[tamerj1]

Hi @lawada4 

please use

amount purchased value =

SUMX (
VALUES ( 'user portfolio'[Ticker] ),
CALCULATE ( SUM ( 'user portfolio'[number of stocks purchased] ) ) * [last closing price]
)

[AilleryO]

Hi,

As mentionned by @Syk the total of a table in Power BI is the same calculation as the one for the lines/rows except the filter coming from the lines.

To get back the Row context (which I think you need), you should replace your SUM by SUMX to restore a row by row calculation (iteration).

To get more details, you can watch the video.

Hope it helps and do not hesitate to share your formula with other members that could face the same situation or tell us if you need more help.

[Syk · Accepted solution]

The total column does not by default sum the total of the rows above in Power BI. Alberto explains it much better than I could attempt to - https://youtu.be/6rgAkejrup8

[lawada4]

SUMX has solved the problem.

i changed the measure to:

amount puchased value =

SUMX(

    VALUES('stocks data'[Ticker]),

 

CALCULATE(SUM('user portfolio'[number of stocks purchased])*[last closing price])

)

thank you for sending the link! it was quite informative.