Generate a table which combines summarised data from multiple tables.

[MicrobicTiger]

Hi, I am new to Power Bi and am trying to generate a table that combines summarised data from multiple tables.

New Table= ADDCOLUMNS(
    UNION(
        SUMMARIZE(
            FILTER(
                'Purchased',
                'Purchased'[Units] > 1
            ),
            'Purchased'[Account],
            "Total Units", SUM('Purchased'[Units])
        ),
        SUMMARIZE( 
            FILTER(
                'Users',
                'Users'[Start Time].[MonthNo] = EOMONTH(TODAY(), -1)
            ),
        'Users'[Account],
        "Last Month", COUNT('Users'[Name])
        )
    ),
    "Last Month", COUNT('Users'[Name])
)

The result gives me a count of units by account, but each row is the total of Users.

| Account | Total Units | Last Month |
|:-------:|:-----------:|:----------:|
|Name 1 | 12 | 8000 |
|Name 2 | 63 | 8000 |
|Name 4 | 42 | 8000 |

How do I get the summarised result in my Last Month field?

[v-jingzhang]

Hi @MicrobicTiger 

Do you want to summarize Total Units from 'Purchased' table into "Total Units" column, and summarize Last Month User Count from 'Users' table into "Last Month" column? If so, you don't need to use UNION function.

In addition, what does 'Users'[Start Time].[MonthNo] return? If it returns a month number, it cannot be compared to EOMONTH(TODAY(), -1) because the latter one returns the last date of last month.

Based on above, the expected result of user count summarization from 'Users' table is not very clear. Can you please provide some sample data as well as the expected result in table format?

Besides, assume the 'Users' table is like below,

you can try 

New Table = 
ADDCOLUMNS (
    SUMMARIZE (
        FILTER ( 'Purchased', 'Purchased'[Units] > 1 ),
        'Purchased'[Account],
        "Total Units", SUM ( 'Purchased'[Units] )
    ),
    "Last Month",
        COUNTX (
            FILTER (
                'Users',
                'Users'[Account] = EARLIER ( 'Purchased'[Account] )
                    && 'Users'[Start Time] = EOMONTH ( TODAY (), -1 )
            ),
            'Users'[Name]
        )
)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.