summarize()

3 Topics

DAX
Bonjour les experts dax j'ai importé une table 'sinistres_regles' qui est une jointure de tables en SQL j'ai écrit une mesure pour calculer le nombre de dossier en stock, le résultat match bien avec l'équivalent de ma requête SQL j'ai écrit une mesure pour calculer le coût des sinistres, le résultat matche avec celui de la requête SQL existante par contre quand je calcule le coût des sinistres en stock , le résultat est très différent de celui de la BD. Mes requêtes SQL utilisent les mêmes jointures de tables que ma table 'sinistres regles' dans POWER BI comme base du FROM. je joins à mon message les captures des scripts dxa que j'ai écrit.
Solved
PASCALESAAR
8 months ago Place DAX Commands and Tips
2.8KViews
0likes
10Comments
Unable to use AddColumns Summarize with Calculated Dimension
I am working with both forecasts and actuals, and I would like to create a version which is called "Forecast + Actuals" which is a combination of actuals and the forecast values which is after the last date of actuals. I know you can do this via a measure like if selected value = "Forecast + Actuals" then return calculate measure, but I would prefer to do it in one measure to reduce the complexity of my measure tree. Here is an example data set. Date Version Value 31-Oct Forecast 100 30-Sep Forecast 150 30-Sep Actual 120 31-Aug Actual 100 Here is the Version Dimension table. "Forecast + Actuals" doesn't exist in my fact table, but can be derived from the fact table through a combination of Forecast and Actuals. Version Dimension Table Forecast Actuals Forecast + Actuals I would like a measure which would populate the following: Version 8/31 9/30 10/31 Forecast 150 100 Actuals 100 120 Forecast + Actuals 100 120 100 I was trying to accomplish it like this via this measure: DataSum = VAR MaxActualsDate = CALCULATE ( MAX ( FactTable[Date], Version = "Actuals" ) ) VAR SummaryData = ADDCOLUMNS ( SUMMARIZE ( 'FactTable', 'Date'[Date], Versions[Version] ), // this is the core table "@SourceValue", CALCULATE ( SUM ( FactTable[Value] ) ) ) RETURN IF ( SELECTEDVALUE ( Versions[Version] ) = "Current Forecast + Actuals", CALCULATE ( SUMX ( SummaryData, [@SourceValue] ), [Version] = "Actuals" ) + CALCULATE ( SUMX ( SummaryData, [@SourceValue] ), [Version] = "Forecast", Date[Date] > MaxActualsDate ), SUMX ( SummaryData, [@SourceValue] ) ) I didn't find any success here - so then I decided to modify the variable table by unioning the "Forecast + Actuals" into the SummaryData variable table. When i look at this in DAX studio, the table does have "Forecast + Actuals" with values, however, I am unable to bring this into a visual. DataSum = var MaxActualsDate = calculate( max(FactTable[Date],Version="Actuals") Var SummaryData = ADDCOLUMNS ( TREATAS ( UNION ( SUMMARIZE ( 'FactTable', 'Date'[Date], Versions[Version] ), // this is the core table ADDCOLUMNS ( SUMMARIZE ( FactTable', 'Date'[Date], Versions[Version] ), "Version", "Current Forecast + Actuals" // this is where i'm trying to add the "fake data series" ) ), 'Date'[Date], Versions[Version] ), "@SourceValue", IF ( [Version] = "Current Forecast + Actuals", CALCULATE ( SUM ( FactTable[Value] ), ALL ( Versions ), Versions[Version] = "Actuals" ), CALCULATE ( SUM (FactTable[Value]) ) ) Return Sumx ( SummaryData, [@SourceValue])
Solved
avachris
3 years ago Place DAX Commands and Tips
504Views
0likes
1Comment
Aggregated Total using a Distinct Totals
Hello PBI Community, I need help to figure out how I can calculate a Aggregated Total using Distinct Totals. Specifically , the Denominator to calculate a Percent of total. Unfortunately , my table has duplicates so I tried the following: VAR Asum= SUMX( SUMMARIZE( VALUES(Table1[AcctNum]) ,Table1[AcctNum] ,"ABCD" ,MIN(Table1[Amount]) ) ,[ABCD] ) RETURN SUMX(ALL(Table1[Column1]), Asum )
Solved
ae19bu
6 years ago Place DAX Commands and Tips
1.1KViews
0likes
1Comment