DAX to flatten incoming sales forecast by month with correct subtotals
I have a dataset that projects incoming sales forecast but the day of the month the forecast is projected for is not accurate to when the sales will actually occur. I want to "smooth" the forecast data by calculating the average for the month and assigning it to each day. The ultimate goal is to be able to calculate totals based on past real data and future forecast data and be able to have the subtotals calculate correctly even when "today" is within the summarized period of time. At the moment I am able to get the "Daily Average Forecast" and assign it to the first day of the month, but I am struggling to have it apply to each day of the month and to get the sub-total to be a sum of the previously calculated daily values. I would share code I have written so far but I have tried so many permutations of SUMMARIZECOLUMNS and SUMX that I can't remember what worked best already. I am using Melissa de Korte's Extended Date Table and have Time Intelligence switched off in my model and implicit calculations are disabled.
SUMX 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? Thanks
LOD and measure totals
I am stuck trying to maintain the same measure total when removing a column from a table visual. Here is my current measure, which gives the correct result and the correct total: BD SUM = VAR A = SUMMARIZE ( Sheet1, Sheet1[brand], "Avg", ( AVERAGE ( Sheet1[income] ) * Income[Income Value] ) + ( AVERAGE ( Sheet1[education] ) * Education[Education Value] ) + ( AVERAGE ( Sheet1[age] ) * Age[Age Value] ) + ( AVERAGE ( Sheet1[ethnicity] ) * Ethnicity[Ethnicity Value] ) + ( AVERAGE ( Sheet1[house] ) * 'Household Size'[Household Size Value] ) + ( AVERAGE ( Sheet1[marital] ) * 'Marital Status'[Marital Status Value] ) + ( AVERAGE ( Sheet1[children] ) * 'Presence of Children'[Presence of Children Value] ) ) RETURN SUMX ( A, [Avg] ) When adding this measure to a table visual, it generates this result: id brand BD SUM 9007900349 A 2.45 9007900349 B 1.63 9007900349 C 2.4 9007900349 D 2.5 9007900349 E 1.88 The total of all 5 values is 10.86 which is correct, however, when I remove the 'brand' column from the table visual, it gives the wrong total. Any ideas how I can produce this result? id BD SUM 9007900349 10.86 I have tried every iteration of ALL, ALLEXCEPT, and VALUES I can think of but nothing seems to work. Appreciate the help!DAX Optimization SUMX ( SUMMARIZE ) - Performance Issue
I'm not able to optimize this measure. I'm new to Power BI DAX. Kindly suggest me a way to run this DAX faster. If I am wrong, please suggest me any other alternative way to achieve the below DAX DAX Measure: Measure name = SUMX ( SUMMARIZE (table_name', table_name'[Col1],table_name'[Col2],table_name'[col3],table_name'[col4],table_name'[col5], "result", CALCULATE ( DISTINCTCOUNTNOBLANK ( table_name'[col1] ), FILTER ( table_name', SUM ( table_name'[Counter] ) = 1 ) ) ), [result] )
