Creating a single row with the sum of serveral rows with the same value in a reference column

[Wonder]

Hi,
I have the following query results:

I want to create one row for China with the sum of all the values for China on a given day. In this case the row results would look like this:
Western Pacific   |     China     |     278    |      1/20/2020

How do I alter my query to acheive this?

[Razwan · Accepted solution]

@Wonder 
Ok, sorry for misunderstanding.
You can do that as well in Power Query.
Just go in Transform - Group By then select Advanced  (in order to group by multiple columns) and choose your columns along with the aggregated one.

Afterwards, if you want the results for China only, you can filter the Country column to China.

Kind regards,
Razwan

[Razwan]

@Wonder 
Hi, you can achieve this very easily using DAX.
Just create a measure for total cases per date:

# Total Cases = 
    CALCULATE(
        SUM(YourTable[Confirmed Cases]),
            ALLEXCEPT(YourTable, 
                YourTable[WHO Regional Office], 
                YourTable[Country], 
                YourTable[Reporting Date]
            )
    )

Then a calculated table using the measure previously created:

CalcTable = 
    SUMMARIZE(
        FILTER(
            YourTable,
            YourTable[Country] = "China"
        ),
        YourTable[WHO Regional Office],
        YourTable[Country],
        YourTable[Reporting Date],
        "Total Cases", [# Total Cases]
    )
        

Please let me know how it works.

Kind regards,
Razwan

[Wonder]

@Razwan 

I am looking to alter the query to retreive the data correctly not show the data in a report in the right format. The data will later be appened to another query or queries. So it has to match the signle row format. I cross posted on the power query forum as well. Thanks for your solution, but I am wondering how to alter my query.

[Razwan · Accepted solution]

@Wonder 
Ok, sorry for misunderstanding.
You can do that as well in Power Query.
Just go in Transform - Group By then select Advanced  (in order to group by multiple columns) and choose your columns along with the aggregated one.

Afterwards, if you want the results for China only, you can filter the Country column to China.

Kind regards,
Razwan

[Wonder · Accepted solution]

@Razwan This appears to have solved the problem. Thanks for suggesting the Advanced radio button, I had missed it in my previous attempts using this feature.