dax calculation total is incorrect
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 🙂
Total on dynamic measure
Hi I'm working on a dataset for sales dept. In our business we have both internal and external sales both present in the datamodel, summed up in a single measure. I have a Dimension named transaction_type that contains a value for internal/external The business would like that the sales measure show only external sales, unless filtered on internal, or transaction type being used within a visualization. Using SelectedValue I can build the measure to show only external unless filtered on internal or shown in a visualization. Sadly this shows the wrong total in the cases where both internal and external is present. I understand why the total is showing as only External (because the total is not a selectedvalue and therefore reverts to "Eksternal") Is there a way to force the total to sum up what is actually shown on screen?
Help me Understand why my Total column isn't working
Below is the measure and above is the values table (contracts x Fiscal Year x Cost per FY) My values are all correct -- giving me the $$ that each contract will pay during the relavent FYs. However, the Total column is very wrong. Appears to be giving me conCost / numDays. I would love some support to understand why this is happening and how to fix it. (Filter FY Range returns 1 for each FY during the Contract date range. Returns blank otherwise)
Total value coming from measure is wrong
Hello, I developed this measure: CALCULATE(if([COUNTReport]=2, DIVIDE(SUM(FactOTDJnJPap[measurevalue]),2), SUM(FactOTDJnJPap[measurevalue])), FactOTDJnJPap[IDTypeReportJnJPharma] IN {1,5}) The row values are correct but the total is wrong, the value should be around 9,2. In the next image I explain better what I have now coming from the Fact and what I need to calculate. I have a line chart with the values for every Site (this one is showing the correct values) and another one with the total value divided by the number of sites (it's not always the same number) for that month. Really appreciate your help, I'm stucked with this for 2 days. Thanks is advance.Sum measure instead of calculate
Hi all, I want to sum the total meassurement instead of calculating it. My data looks similar to this Account number KAM Balance Unpaid Item Avg. price pr. item Theoretical value (unpaid*avg.price) 1 A 10.000 250 150 37.500 2 b 15.000 0 100 0 3 c 5.000 15 350 5.250 Total 30.000 265 200 53.000 I want it to sum the total in "theoretical value" instead of calculating it, so the value should be (37.500+0+5.250) 42.750 instead of (265*200) 53.000 Right now I have tried following Dax code Theoretical value test = if( HASONEFILTER(''[Account Number]),SUM(unpaid item) * AVERAGE(avg. price pr. item), ???????))) I dont know what to write instead of ????, to make it sum instead of calculate. Can you guys help me? 🙂 Thanks.
Surprised : Values not adding up for few dates in the table
Dear All, I am trying to simply find the TOTAL of 'OT Attach' business and the problem i am facing is values for date 4/17/2020 is not addaing up. Value for 4/27/2020 is 100K less. The data snap shot and the DAX used is listed below. If i simply copy the data from Power bi table to excel the total adds up correctly to $814 K but when i sum it up using DAX it is showing only $614 K. Kindly help me. The DAX i am using to total the column is as below. I tried 2 DAX. Both are giving me same results. DAX # 1 used to sum the table is : OT Attach-TCV = sum('Master-Funnel (2)'[OT Attach-TCV]) DAX # 2 used : OT Attach-TCV = VAR __table = SUMMARIZE('Master-Funnel (2)','Master-Funnel (2)'[Close Date],"_Value",sum('Master-Funnel (2)'[OT Attach-TCV])) RETURN IF(HASONEVALUE('Master-Funnel (2)'[Close Date]),sum('Master-Funnel (2)'[OT Attach-TCV]),SUMX(__table,[_value]))
