How to summarize and create new table all in one step

[eliasayyy]

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

[Anonymous]

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 !

[tamerj1 · Accepted solution]

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