Forum Discussion

esmat's avatar
esmat
Regular Visitor
3 years ago
Solved

summarize a table

Hi All,

I have a table with multi currency conversion factors, would like to summarize this table based on monthly average while maintaining all columns.

 

table is below 

 

 

6 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    You could use something like

    Monthly summary =
ADDCOLUMNS (
    SUMMARIZE ( 'Currency table', 'Date'[Year month] ),
    "Avg USD", CALCULATE ( AVERAGE ( 'Currency table'[Value.USD] ) ),
    "Avg JPY", CALCULATE ( AVERAGE ( 'Currency table'[Value.JPY] ) )
)
    • esmat's avatar
      esmat
      Regular Visitor
      3 years ago

      I got an error message as below

       

       

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi esmat 
    Of course you need to include all other columns

    Summary Table =
GROUPBY (
    ADDCOLUMNS ( 'Table', "Month Year", FORMAT ( 'Table'[Day], "MMM YYYY" ) ),
    [Month Year],
    "Value.USD", AVERAGEX ( CURRENTGROUP (), [Value.USD] ),
    "Value.JPY", AVERAGEX ( CURRENTGROUP (), [Value.JPY] ),
    "Value.BGN", AVERAGEX ( CURRENTGROUP (), [Value.BGN] )
)
    • esmat's avatar
      esmat
      Regular Visitor
      3 years ago

      Hi tamerj1 

      I obtained an error message as shown in yellow below

       

       

      • tamerj1's avatar
        tamerj1
        Community Champion
        3 years ago

        esmat 
        This is a calculated table not a measure