topic Re: summarize a table in DAX Commands and Tips

summarize a table

esmat — Mon, 24 Apr 2023 09:55:39 GMT

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

Re: summarize a table

johnt75 — Mon, 24 Apr 2023 10:36:24 GMT

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

Re: summarize a table

tamerj1 — Mon, 24 Apr 2023 10:40:44 GMT

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

Re: summarize a table

esmat — Mon, 24 Apr 2023 10:50:24 GMT

I got an error message as below

Re: summarize a table

esmat — Mon, 24 Apr 2023 10:54:55 GMT

Hi tamerj1

I obtained an error message as shown in yellow below

Re: summarize a table

tamerj1 — Mon, 24 Apr 2023 11:37:56 GMT

esmat
This is a calculated table not a measure

Re: summarize a table

esmat — Mon, 24 Apr 2023 13:08:03 GMT

thank you very much

it worked perfectly