Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello!
I'm trying to create a new table from another.
Table 1: each row is a date (from 1/1/2014 to today)
I need a table that summarizes the data:
- New table: each row is a month
- Summarized for year and month
- 3 metrics, average of PrecioPlatts, average of EuroDolar and average of BrentCierre for each month-year.
I've tried to summarize years and months in a new table, but I have problems with the metrics.
Table = SUMMARIZECOLUMNS(ADMINISTRACION_FuelDivisas[Año],ADMINISTRACION_FuelDivisas[Mes])
Could anybody help me?
Thank you very much 🙂
Luis
Solved! Go to Solution.
Hi @lmatera,
Do you really need to build another table with value pre calculated ?
You could simply enrich you Power Pivot data model by :
1. adding calculated columns to build and year and month attributes
Year = YEAR([Fecha] YearMonthCode = FORMAT([Fecha],"yyyyMM") YearMonthLabel = FORMAT([Fecha];"yyyyMMM")
2. ordering the column YearMonthLabel by YearMonthCode
3. adding new measures to aggregate your metrics as you want
AveragePrecioPlatts = AVERAGEX('Test',[PrecioPlatts]) AverageEuroToDolar = AVERAGEX('Test',[PrecioPlatts] * [EuroToDolar]) AverageBrentCierre = AVERAGEX('Test';[BrentCierre]
And just build you matrix.
Hi @lmatera,
Do you really need to build another table with value pre calculated ?
You could simply enrich you Power Pivot data model by :
1. adding calculated columns to build and year and month attributes
Year = YEAR([Fecha] YearMonthCode = FORMAT([Fecha],"yyyyMM") YearMonthLabel = FORMAT([Fecha];"yyyyMMM")
2. ordering the column YearMonthLabel by YearMonthCode
3. adding new measures to aggregate your metrics as you want
AveragePrecioPlatts = AVERAGEX('Test',[PrecioPlatts]) AverageEuroToDolar = AVERAGEX('Test',[PrecioPlatts] * [EuroToDolar]) AverageBrentCierre = AVERAGEX('Test';[BrentCierre]
And just build you matrix.
It works perfectly... thanks 😃
Nice job 🙂
Is this for a report or to create another table in the report?
The matrix visualization is what you want for a report, and you can create the three measures you need with the AVERAGE() function in DAX.
If you need it as a separate table in your data model for some reason, then you'll use the same three measures as above in the following:
// DAX // Calculated column in source table Year = YEAR( 'SourceTable'[Fecha] ) Month = MONTH( 'SourceTable[Fecha] ) // Calculated Table SummarizedTable = ADDCOLUMNS( SUMMARIZE( 'SourceTable' ,'SourceTable'[Year] ,'SourceTable[Month] ) ,[Promedio de PrecioPlatts] ,[Promedio de EuroToDolar] ,[Promedio de BrentCierre] )
@greggyb any advantage to doing this with DAX over creating a reference table in the Query Editor and summarizing with Group By?
I've found that with large tables that Power Query is much slower at this sort of aggregation than the Tabular engine powering the data model.
Functionally, no difference.
Storage space / RAM use - benefit to Power Query as compression is better for non-calculated fields and tables in Tabular, but a summarized table is expected to be pretty small anyway.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |