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 🙂
ADDCOLUMNS and SUMMARIZE - incorrect Total (Live Connection)
Please see below issue: As you can see, for the No of Sessions, the total should be 9, but i'm getting a total of 32 insead which is super weird. Since we're using a Live Connection, I don't have access to calculated columns. See below DAX I used: No of Sessions = VAR SessionsTable = ADDCOLUMNS ( SUMMARIZE ( 'Location-Practice-Rendering', 'Location-Practice-Rendering'[LeafName] ), "Total Encounters", [Encounters], "No of Days Total", [No of Days], "No of Sessions Per Day", IF ( [Encounters] <= 2, 0, IF ( [Encounters] > 10, 2, 1 ) ) ) RETURN SUMX( SessionsTable, [No of Days Total] * [No of Sessions Per Day]) Where [Encounters] and [No of Days] are measures: Encounters = CALCULATE ( [Amount], FILTER ( ALL ( 'Profitability Account' ), 'Profitability Account'[Description] = "Encounters" )) No of Days = CALCULATE ( DISTINCTCOUNT ( 'Time'[CalendarDate]), FILTER ( 'Time', 'Time'[DayOfWeekdayDescription] <> "Sunday" && 'Time'[DayOfWeekdayDescription] <> "Saturday" ) ) We created the following DAX measure which gives us the correct results, BUT it takes hours to load, where as my DAX above takes seconds. The following DAX gives us correct results: No Of Sessions Day = VAR _encounters = CALCULATE ( DISTINCTCOUNT ( 'Transaction Attributes'[Encounter Number__EBM__Encounter Number] ), FILTER ( 'Transaction Attributes', 'Base Measures'[Encounters] > 0 ) ) RETURN SWITCH ( TRUE (), _encounters <= 2, 0, _encounters >= 2 && _encounters <= 10, 1, _encounters > 10, 2 ) Total No of Sessions = VAR SessionsTable = SUMMARIZE ( 'Fact', 'Fact'[CustomerID], 'Fact'[CalendarDate], "No of Days Total", CALCULATE ( DISTINCTCOUNT ( 'Fact'[CalendarDate] ) ), "No of Sessions Per Day", [No Of Sessions Day] ) RETURN SUMX ( SessionsTable, [No of Days Total] * [No of Sessions Per Day] ) Here are the results from the above DAX: Can you please help me amened my original DAX so I can get the correct results? The ADDCOLUMNS SUMMARIZE works a lot faster. Unless we can fix the second DAX to make it faster? amitchandak - you have any thoughts on this issue? Any help would be much appreciated 🙂
TOTAL VALUE CALCULATION - WRONG
Dear colleagues, I have a problem with de total value. The column "Primeiros Caracteres" is from a dimension table and the other colums below are measures related to this table. My problem is on last column (CountTipodeCriterioAlterado) that is calculating very well (1 to Items that changed from EX to PQ or from PQ to EX and 0 to same Crtiério (PQ to PQ and EX to EX). But i need to get the sum of this number (All Items that had chaged), but the total shows 0, not the sum. The formulas are below: CountTipodeCritérioAlterado = IF([TipoCritériodCicloAtual]= "PQ" && [TipoCritériodCicloAnterior]="EX" ,1, IF([TipoCritériodCicloAtual]=BLANK(),0, IF([TipoCritériodCicloAnterior]=BLANK(),0, IF([TipoCritériodCicloAtual]= "EX" && [TipoCritériodCicloAnterior]="EX" ,0, IF([TipoCritériodCicloAtual]= "PQ" && [TipoCritériodCicloAnterior]="PQ" ,0))))) Thanks for helping
Total value wrong in table
Hi all!! I have this meansure bellow, when this mensure is in line is write, but the total value is wrong. Someone know how could I do this ?? Thank so much, VAR MaxDate = MAX(fEstoque[OrderDate]) VAR Result = CALCULATE( [Desvio Consumo] * [Custo Standard], fMovimentacoes[OrderDate]<=MaxDate, ALL( fMovimentacoes[OrderDate]) ) Return Result
