Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
XaverScheu70
New Member

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

Hi @XaverScheu70

 

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


Regards
Zubair

Please try my custom visuals

View solution in original post

4 REPLIES 4
Zubair_Muhammad
Community Champion
Community Champion

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

Regards
Zubair

Please try my custom visuals

 

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

Hi @XaverScheu70

 

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


Regards
Zubair

Please try my custom visuals

Done, works perfectly!

 

Than you @Zubair_Muhammad!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.