DAX formula doesn't sum correctly
Hi all, I have been trying to figure out what I am doing wrong in this DAX formula. I have a normal sales table where I have cost, quantity and sales. Sales = qty x cost but now the following formula calculates the yellow ones and not the greeen ones I would like to sum. What can be seen in the screen shot, the price can change and the correct total sales here would be 229 710€ This is my current formula, any help how to fix this would be great. Thank you. Sales € = SUMX(VALUES('Value Entries'), [Qty x Purch Price]) Br MattSUMX SUMMARIZE group by company in a table
Hi all. I have a simple table made of 2 fields and 1 measure: where _Sales Denominator YA ORG = CALCULATE( [_Sales YA ORG] , ALL(Company[Company], Company[Brand],Company[Sub Brand], Company[Variant]) , ALLSELECTED(Period[Period]) ,ALLSELECTED('Market Breakdown'[Country]) ,ALLSELECTED('Organic Claim'[Organic Claim]) , VALUES('W Segment'[W Segment]) , VALUES('Market Brkdown'[Hier Level Name]) , VALUES('Ad Hoc Analysis View'[Category]) , VALUES('Ad Hoc Analysis View'[Segment]) ) what I want to do is to delete the country field and obtain one row for each company with the sum of each _Sales denominator YA ORG for each company. For example I want this result: Company _Sales denominator YA ORG PRIVATE LABEL 3402952 STRAUSS GROUP 206 TCHIBO 36854 TONYS COFFEE ROASTERS 2895 VERGNANO 1131 WERTFORM 3987 WICKED JOE LLC 23592 WOSEBA 1035 So what I've tried is to modify the measure this way: _Sales denominator YA ORG= SUMX( SUMMARIZE( 'Ad Hoc Analysis View', 'Ad Hoc Analysis View'[Company], "x_per_company", CALCULATE( [_Sales YA ORG], ALL(Company[Company], Company[Brand],Company[Sub Brand], Company[Variant]), ALLSELECTED(Period[Period]), ALLSELECTED('Market Brkdown'[Country]), ALLSELECTED('Organic Claim'[Organic Claim]), VALUES('W Segment'[W Segment]), VALUES('Market Brkdown'[Hier Level Name]), VALUES('Ad Hoc Analysis View'[Category]), VALUES('Ad Hoc Analysis View'[Segment]) ) ), [x_per_company] ) But I obtain this table, with summarized values that are different from what I expect: Do you have any suggestion? ThanksCalculate variances between various columns depending on the value date vs current date
Hello, I have two tables, one is a Target Amounts table which has a Target Amount and an Outlook Amount. The other one is an Actuals Amounts Table, that has the Actual Amounts in it (Both shown below). These fact values have corresponding 1-many relationships with some dimension tables, so all data can line up as needed. I need to write a measure that returns a value based on the following logic: If the amounts are within 6 months of the current date then: IF: Target Amount > Actuals Amount, return Target Amount - Outlook Amount Else, Return Actuals Amount - Outlook Amount Else, return Actuals Amount - Outlook Amount Intended result: So, if the current date is July 2023, then the measure would return the results in the far right column in the picture below. This is the formula I tried. The results are significantly larger than what was intended (hundreds of Billions in variances when it should be 10s of millions in variances). Any ideas on what I need to tweak? OutlookVarianceCalculation = //Create variables VAR CurrentDate = TODAY() //Current date variable VAR SixMonthsAgo = EDATE(CurrentDate, -6) //6 months prior to current date VAR TargetAmount = SUM(Cumulative_Targets[Current Month Net Target]) //Target amount VAR ActualsAmount = SUM(CMS_Gross_Actuals[Net_Amount]) //Actuals Amount VAR OutlookAmount = SUM(Cumulative_Targets[Outlook]) //Outlook Amount RETURN SUMX( FILTER( //Filter table on dates within the past 6 months ALL('Date Dim'), 'Date Dim'[First_Of_Month] >=SixMonthsAgo && 'Date Dim'[First_Of_Month] <= CurrentDate ), IF( //Return either target vs outlook variance or actuals vs outlook variance based on the logic below TargetAmount > ActualsAmount, //logic TargetAmount - OutlookAmount, // return if true ActualsAmount - OutlookAmount // return if false ) ) + SUMX( //Also sum variances past 6 months using the actuals - outlook logic. FILTER( ALL('Date Dim'), 'Date Dim'[First_Of_Month] < SixMonthsAgo || 'Date Dim'[First_Of_Month] > CurrentDate ), ActualsAmount - OutlookAmount ) Tables: Actuals Table Service Month Posting Date Region Sub Region Reporting Category Actuals Amount 1/1/2023 0:00 1/1/2023 0:00 Region 1 Cat 1 $503,972 2/1/2023 0:00 2/1/2023 0:00 Region 1 Cat 1 $1,099,065 1/1/2023 0:00 3/1/2023 0:00 Region 1 Cat 2 $86,573 2/1/2023 0:00 4/1/2023 0:00 Region 1 Cat 2 $10,277 2/1/2023 0:00 5/1/2023 0:00 Region 1 Cat 2 ($42,746) Target Table Service Date Region Sub Region Reporting Category Target Amount Outlook Amount 1/1/2023 0:00 Region 1 Cat 1 $600,000 550,000.00 2/1/2023 0:00 Region 1 Cat 1 $1,000,000 900,000.00 1/1/2023 0:00 Region 1 Cat 2 $80,000 90,000.00 2/1/2023 0:00 Region 1 Cat 2 $10,000 10,000.00 2/1/2023 0:00 Region 1 Cat 2 ($45,000) (50,000.00) Table Relationships: Each dimension has a dimension table that has a 1-Many relationship with the fact table. The dates used in my dax formula come form my date dimension table.
SUMX Help
I created a measure using SUMX to get the number of days between two dates so that I could see how many days it takes an account manager to approve something once it's submitted for approval. However now I am seeing issues with it. Some of the results "Lag" are correct but some say "112" lag but the dates are not 112 days in between. I used: Measure = SUMX(Table1, DATEDIFF(Table1 [Physical Date],Table1 [PriceApproved.date], DAY))
EFFICIENCY FORMULA WITH DAX
Hi guys, Can you please help me, I´m trying to do this efficiency formula "% EFICIENCIA = VAR _a = SUMMARIZE ( 'pcc', 'pcc'[Hora inicial], 'pcc'[No. Unidades en la hora], 'pcc'[Linea de empaque] ) VAR _b = ADDCOLUMNS ( _a, "%", IF ( ( [tiempo teorico] / (sum(pcc[Total de horas])*60) > 100), "0", ( [tiempo teorico] / (sum(pcc[Total de horas])*60) ) )) RETURN SUMX( FILTER ( _b, (sum(pcc[Total de horas])*60) <> 0 && [tiempo teorico] <> BLANK () ), [%] )" But the formula sometimes shows me values over 100%, and this alters my reports. How can I include the condition that if the result is over 100%, it shows 100% and no more? Hope you guys can help me! Regards, Valentina
SUMX Different Column Depending On IF Condition
Hello All, I am struggling with a measure that is trying to accomplish the following: I have a set of ID_Tasks attributed to ID_Project and I want to find the TotalSales by summing either TotalSalesSingle or TotalSalesMulti depending on if my dynamic selection (slicer on ID_Task) contains duplicate ID_Project or not. I have a first measure that checks if my selection contains duplicates or not, and counts them. Duplicates = CALCULATE(COUNT(SalesProfiles[ID_Project]), ALLSELECTED(SalesProfiles), VALUES(SalesProfiles[ID_Project])) Basically, I am looking for the following results: 1. If I select 2 ID_Task that are from different ID_Project, I want to sum the TotalSalesSingle value for each. 2. If I select 2 ID_Task that are from a shared ID_Project, I want to sum the TotalSalesMulti value for each. 3. If I select all 3 ID_Task , 2 of which share a ID_Project, and 1 does not; I want to sum the TotalSalesMulti for the 2, and TotalSalesSingle for the 1. aka it will sum values from either TotalSalesSingle or Multi depending on Duplicates = 1 or != 1, evaluated for each row. Scenarios: Single Selection: ID_Project is unique value, returns TotalSalesSingle for Total Sales Multi Selection: ID_Project is non-unique value, returns TotalSalesMulti for TotalSales Here you can see that my measure TotalSales doesnt work. I'd like it to return 231 & 351 (TotalSalesMulti column) Multi Selection: All 3 selected. Doesn't work either. In this case I'd like it to return TotalSalesMulti (231 & 351) for ID_Project = 38515, and TotalSalesSingle (856) for ID_Project = 29677. This is my current measure for TotalSales which is far from working. Total Sales = SUMX ( ProjetList, IF( [Duplicates] = 1, SUMX ( SalesProfiles , SalesProfiles[TotalSalesSingle] ) , SUMX ( SalesProfiles , SalesProfiles[TotalSalesMulti] ) ) ) I've included the files below. https://1drv.ms/f/s!AndVpJp2VQectSrHRNEZnOIC2Kqj?e=CA0QS6 Please let me know what you think, and if I wasn't clear about anything. Thank you!
totals help
Hi There I have a measure where if I don't have an actual I use a forecast, this measure works well at the monthly level , but I am not sure why I can't get my total to work. Actuals - $8,335,806 (Jul to Oct), Forecast - $19,854,201 (Nov to Jun) Total should be $28,190,007 for col ACT/FCT My formula is ACT / FCT = var actuals = sumx('FY23 Data','FY23 Data'[ACT]) var forecast = sumx('FY23 Forecast','FY23 Forecast'[FCT]) var result = if(actuals=blank(),forecast,actuals) return result Thanks All
