Calculating Total of a column inside virtual table
Greetings everyone, I have virtual table that looks like this: Each row is a product that belong to same product family (data is only partially visible here). "Revenue" and "Total Regional List" Price exist in the main Data Table. Next calculateions are virtual, used as measures. 1) I have first calculated total Revenue for this family, which is 858.317,55. 2) Next i have calculated "Level Factor Weight" for each item, which is "Revenue" / "Sales Revenue per Year by Family". 3) And finally i have calculated "Weighted Target List Price", which is "Total Regional List Price" * "Level Factor Weight". So far so good. Now i would like to calculated total or sum of "Weighted Target List Price", basically 0,06 + 0,03 + 0,00 + 0,65... (just as one see the numbers inside this table) and that is where i have problems. When i put "Total Regional List Price" and "Weighted Target List Price" in one virtual table without product numbers, they show the same result. Any hint would be very appreciated. Sincerely, PavloDAX Dynamic table / context
Hi, I'm getting stuck on a DAX formula when creating dynamic table based on this requirement: -Getting all rows where the [AsOfDate] is lesser or equal to the date selected -Getting all rows where the [Launch Year]-1 is equal to the year of the date selected -Only getting latest / MAX [AsOfDate] For example: I first created the logic in SQL since I'm more conformable than DAX then try to translate it: CREATE TABLE #TemporaryTable ( LaunchYear INT ,Period INT ,AsOfDate Date ,BCount INT ,ID VARCHAR(4) ); INSERT INTO #TemporaryTable (LaunchYear,Period,AsOfDAte,BCount,ID) VALUES ('2022','1','2/1/2022','12','ASD') ,('2022','2','3/1/2022','5','ASD') ,('2022','3','4/1/2022','3','ASD') ,('2022','4','5/1/2022','2','ASD') ,('2022','5','6/1/2022','123','ASD') ,('2022','6','7/1/2022','14','ASD') ,('2022','7','8/1/2022','16','ASD') ,('2022','8','9/1/2022','1','ASD') ,('2022','1','6/1/2022','9','FGH') ,('2022','2','7/1/2022','3','FGH') ,('2022','3','9/1/2022','4','FGH') ,('2022','1','11/1/2022','12','JKL') ,('2022','2','12/1/2022','5','JKL') ,('2022','3','1/1/2023','3','JKL') ,('2022','4','2/1/2023','2','JKL') ,('2023','1','1/1/2023','0','XXX') DECLARE @DateVar DATE = '2023-01-01'; --'2023-02-01'; --'2023-01-01'; SELECT LaunchYear ,Period ,AsOfDAte ,BCount ,ID FROM ( SELECT * ,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID, AsOfDate DESC) RowNb FROM #TemporaryTable WHERE AsOfDate <= @DateVar AND LaunchYear = YEAR(@DateVar) -1 ) A WHERE RowNb = 1 DROP TABLE #TemporaryTable; Above SQL is behaving as expected and I arrived to this DAX formula: Dynamic Table = VAR FilterTable = FILTER( 'Sheet1', 'Sheet1'[AsOfDate] <= [DateSelected] && 'Sheet1'[LaunchYear] = YEAR([DateSelected])-1 ) VAR AddRowNumber = ADDCOLUMNS( FilterTable, "RowNb", ROWNUMBER ( FilterTable, ORDERBY ( 'Sheet1'[ID], ASC, Sheet1[AsOfDate], DESC), PARTITIONBY ( 'Sheet1'[ID]) ) ) VAR FilterFirstRow = FILTER(AddRowNumber,[RowNb]=1) VAR Result = SELECTCOLUMNS( FilterFirstRow, "RowNb",[RowNb], "AsOfDate",Sheet1[AsOfDate], "BCount",Sheet1[BCount], "ID",Sheet1[ID], "LaunchYear",Sheet1[LaunchYear], "Period",Sheet1[Period] ) RETURN Result It's working fine when I evaluate it and 'hardcode' the date that is filtered: But it's not working when I create it on the report view: - DateSelected measure seems correct - DateSelected = SELECTEDVALUE(Sheet1[AsOfDate]) - Tab result is all wrong The slicer is having a Year-Month column from my Date table that seems to have a proper relationship with the 'Sheet1' table I guess my issue is somewhere around my DateSelected measure / relationship with the Date table not able to have the correct context but I don't understand why obviously.
