Forum Discussion

lmatera's avatar
lmatera
Frequent Visitor
10 years ago
Solved

Summarize data into new table

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

 

 

Need Help
  • fbrossard's avatar
    fbrossard
    10 years ago

    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.

6 Replies

  • greggyb's avatar
    greggyb
    Resident Rockstar
    10 years ago

    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's avatar
      ryans
      Helper I
      10 years ago

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

      • greggyb's avatar
        greggyb
        Resident Rockstar
        10 years ago

        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.

  • fbrossard's avatar
    fbrossard
    Kudo Commander
    10 years ago

    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.