CODEWITHSUNDEEP
powerbigroupingdax
user1922364
user1922364

Reputation: 573

Filtering Before Groupingby / Summarize in Powerbi

I am having some troubles in grouping and filtering in powerbi. I want to be clear about the question so, my goal is to make everything on DAX and not using the filters in the reports. I am doing this because I will create other tables that will relate to this one.

So, let´s say I have one table like this called sales:

Order Customer Product Date         Amount    Type
A001  John     TV      01/02/2019   200,00    ok
A002  Mark     Chair   10/02/2019   150,00    ok
A003  John     Bed     15/02/2019   50,00     Cancelled
A004  Mark     TV      17/02/2019   75,00     ok
A004  Susan    Table   19/02/2019   20,00     ok
A005  John     Pillow  21/02/2019   50,00     ok

I want to get another table like this:

Customer   Orders  Last Order   First Order  Amount  Type
John       2       21/02/2019   01/02/2019   250,00  ok
John       1       15/02/2019   15/02/2019    50,00  Cancelled
Mark       2       17/02/2019   10/02/2019   225,00  ok   
Susan      1       19/02/2019   19/02/2019    20,00  ok

So, if I did not considered if the product was cancelled or not, the formula to create another table would be:

GROUPBY = (Sales;
'sales'[Customer]
"orders"; countx(currentgroup();'sales'[order]);
"last_order";MAXX(currentgroup();'sales'[Date]);
"first_order";MINX(currentgroup();'sales'[Date]);
"Amount";SUMX(currentgroup();'sales'[Amount]))

The question is, how can I groupby the type and understand if the order was cancelled or not?

tks

Upvotes: 1

Views: 187

Answers (2)

Alexis Olson
Alexis Olson

Reputation: 40204

Personally, I prefer SUMMARIZECOLUMNS to GROUP BY in simple situations as it's less verbose.

Summary =
SUMMARIZECOLUMNS (
    Sales[Customer],
    Sales[Type],
    "orders", COUNT ( Sales[Order] ),
    "last_order", MAX ( Sales[Date] ),
    "first_order", MIN ( Sales[Date] ),
    "Amount", SUM ( Sales[Amount] )
)

Upvotes: 1

RADO
RADO

Reputation: 8148

You can simply add "Type" as a grouping field:

Summarized Sales =
GROUPBY (
    'sales',
    'sales'[Customer],
    'sales'[Type],
    "orders", COUNTX ( CURRENTGROUP (), 'sales'[order] ),
    "last_order", MAXX ( CURRENTGROUP (), 'sales'[Date] ),
    "first_order", MINX ( CURRENTGROUP (), 'sales'[Date] ),
    "amount", SUMX ( CURRENTGROUP (), 'sales'[Amount] )
)

Result:

enter image description here

Upvotes: 0

Related Questions