Sum based on multiple columns

[Anonymous]

Hello,

I am trying to sum the number of occurences by groups of other columns to get an idea of how often they occur. Below is an example of my data is set-up. 

I wish to sum by both product and location such that it results in:

Should a calculation like this be done within the query editor or in DAX?

Thanks guys!

[Anonymous · Accepted solution]

Hello @Anonymous ,

There is a simple solution for your group. You just have to put all there columns Product, Location and Sales in Table and it will automatically group by Product and Location and if you want count than add one measure in table as below.
Group by Sum :-

 

Group By Count :-

Thanks

[Anonymous]

Ah, that worked perfectly. Not sure what issue I had ran into previously too, but I couldn't get that to work for the life of me. Thanks again!

[SteveHailey]

Hello @Anonymous. You could do it either place.
 

In the query editor, you would select the Fruit column, then hold down Ctrl and select the City column. Then on the Transform tab of the ribbon, on the far left choose Group By. The defaults should be what you want in this case. They'll look like this:

And the results will look like this:

With DAX, you could create a New Table, and use code like the following:

Table =
ADDCOLUMNS (
    SUMMARIZE ( Data, Data[Fruit], Data[City] ),
    "Count", CALCULATE ( COUNTROWS ( Data ) )
)

The results will look like this:

-Steve