topic Re: How to summarize and create new table all in one step in DAX Commands and Tips

How to summarize and create new table all in one step

eliasayyy — Tue, 28 Mar 2023 11:25:21 GMT

Hello everyone ,

i want to create a table and then summarize it
i tried

Test = SUMMARIZE( UNION( SELECTCOLUMNS( 'Table', "Fields","Impressions", "Month Name",FORMAT('Table'[Date],"MMMM"), "Total",[Total Impressions] ), SELECTCOLUMNS( 'Table', "Fields","Link Clicks", "Month Name",FORMAT('Table'[Date],"MMMM"), "Total",[Total Link Clicks] ) ), [Month Name], [Fields], "Total", SUM([Total]) )

here are the measures

Total Impressions = SUM('Table'[Impressions]) Total Link Clicks = SUM('Table'[Link Clicks])

My issue is that "SUM([Total])" isnt regognized , it says parameter not the correct type and wont let me do it

i am forced to create the table alone with union then create another new table with summarize

how to do it with one step

Re: How to summarize and create new table all in one step

tamerj1 — Tue, 28 Mar 2023 11:54:15 GMT

Hi eliasayyy
Please try

Test = GROUPBY ( UNION ( SELECTCOLUMNS ( 'Table', "Fields", "Impressions", "Month Name", FORMAT ( 'Table'[Date], "MMMM" ), "Total", [Total Impressions] ), SELECTCOLUMNS ( 'Table', "Fields", "Link Clicks", "Month Name", FORMAT ( 'Table'[Date], "MMMM" ), "Total", [Total Link Clicks] ) ), [Month Name], [Fields], "Total", SUMX ( CURRENTGROUP (), [Total] ) )

Re: How to summarize and create new table all in one step

Anonymous — Tue, 28 Mar 2023 13:10:45 GMT

You can modify your DAX formula to achieve this:

Test =
VAR SummaryTable =
SELECTCOLUMNS(
'Table',
"Fields", "Impressions",
"Month Name", FORMAT('Table'[Date], "MMMM"),
"Total", [Total Impressions]
)
UNION (
SELECTCOLUMNS(
'Table',
"Fields", "Link Clicks",
"Month Name", FORMAT('Table'[Date], "MMMM"),
"Total", [Total Link Clicks]
)
)
RETURN
SUMMARIZE(
SummaryTable,
[Month Name],
[Fields],
"Total", SUMX(SUMMARY(SummaryTable), [Total])
)

In this modified formula, we first create a table called SummaryTable that combines the Impressions and Link Clicks columns and calculates the respective totals using the Total Impressions and Total Link Clicks measures.

Then, in the SUMMARIZE function, we use SUMX to calculate the sum of the Total column in the SummaryTable table. This will give us the total of the summarized values.

I hope this helps!

Let me know if you have any further questions.

Regards,

Jupsimar Singh.

Mark my post as a solution if it helped you !