Summarize data into new table

[lmatera]

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

[fbrossard · Accepted 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.

[lmatera]

It works perfectly... thanks 😃

[fbrossard]

Nice job 🙂

[greggyb]

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]
)
    

[ryans]

@greggyb any advantage to doing this with DAX over creating a reference table in the Query Editor and summarizing with Group By?

[greggyb]

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.