Forum Discussion
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.
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
4 Replies
- Zubair_Muhammad
Community Champion
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] ) )
Hi Zubair_Muhammad,
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
- Zubair_Muhammad
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
