cancel
Showing results for
Did you mean:
Frequent Visitor

## groupby / summarize / filter

Hi,

I have got a query in power bi and the table what I'm trying to group or summarize (but in the same table) has various columns.

"Store", "Style Key", "Style Color", "Sales Qty", "Stock Qty".

We can find more than one Style Key in various Colors, it looks so:

Madrid Store / 123456 / Black / 1 / 38

Madrid Store / 123456 / Grey / 2 / 58

Madrid Store / 123456 / White / 1 / 29

Madrid Store / 125344 / Black / 1 / 23

Madrid Store / 125344 / White / 3 / 43

What I need to group or summarize is:

Madrid Store / 123456 / Black / 1 / 38 / 4 / 125

Madrid Store / 123456 / Grey / 2 / 58 / 4 / 125

Madrid Store / 123456 / White / 1 / 29 / 4 / 125

Madrid Store / 125344 / Black / 1 / 23 / 3 / 66

Madrid Store / 125344 / White / 2 / 43 / 3 / 66

The goal is to take away the color when I sum, and just keep as MASTER the Style Key.

I need to keep the table as above and not in an extra table.

Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User

## Re: groupby / summarize / filter

In that case, we can add Store Column in the ALLEXCEPT arguments

```F1 =
CALCULATE ( SUM ( Table1[Stock Qty] ), ALLEXCEPT ( Table1, Table1[Store] ,Table1[Style Key] ) )

F2 =
CALCULATE ( SUM ( Table1[Sales Qty] ), ALLEXCEPT ( Table1,Table1[Store], Table1[Style Key] ) )```

Here is the documentation of ALLEXCPET function

https://msdn.microsoft.com/en-us/query-bi/dax/allexcept-function-dax

4 REPLIES 4
Super User

## Re: groupby / summarize / filter

@XaverScheu70

You can use these formulas

```F1 =
CALCULATE ( SUM ( Table1[Stock Qty] ), ALLEXCEPT ( Table1, Table1[Style Key] ) )

F2 =
CALCULATE ( SUM ( Table1[Sales Qty] ), ALLEXCEPT ( Table1, Table1[Style Key] ) )```
Frequent Visitor

## Re: groupby / summarize / filter

Yes, it works great!!! Thank you.

But how can I do the same with more then one ALLEXCEPT condicion?

Here below in the STORE COLUMN we have an other condition, Barcelona Store.

Madrid Store / 123456 / Black / 1 / 38 / 4 / 125

Madrid Store / 123456 / Grey / 2 / 58 / 4 / 125

Madrid Store / 123456 / White / 1 / 29 / 4 / 125

Madrid Store / 125344 / Black / 1 / 23 / 3 / 66

Madrid Store / 125344 / White / 2 / 43 / 3 / 66

Barcelona Store / 123456 / Black / 3 / 10 / 5 / 20

Barcelona Store / 123456 / White / 2 / 10 / 5 / 20

Highlighted
Super User

## Re: groupby / summarize / filter

In that case, we can add Store Column in the ALLEXCEPT arguments

```F1 =
CALCULATE ( SUM ( Table1[Stock Qty] ), ALLEXCEPT ( Table1, Table1[Store] ,Table1[Style Key] ) )

F2 =
CALCULATE ( SUM ( Table1[Sales Qty] ), ALLEXCEPT ( Table1,Table1[Store], Table1[Style Key] ) )```

Here is the documentation of ALLEXCPET function

https://msdn.microsoft.com/en-us/query-bi/dax/allexcept-function-dax

Frequent Visitor

## Re: groupby / summarize / filter

Done, works perfectly!

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 63 members 1,176 guests
Recent signins: