Dynamic filtering by criteria in another table

[Bwong]

Hello, 

I am new to BI. I m working with table like below

Date                 Code 1   Code 2   Code 3      Amount 
31/12/2017        A            D              Z             10
31/12/2017        A            D              R              20
31/12/2017        A            D              R              30      
31/12/2017        A            D              R              30      
:
:

I then summarize a new table with unique of Date together with Code 1 Code 2 and Code 3. 
Date                 Code 1   Code 2   Code 3      Amount --> I am unable to use do the summation of this column. 
31/12/2017        A            D              Z             
31/12/2017        A            D              R     

In short, please share if you know how to perform summation of filtering based on the criteria shown in each row. 

Thanks so much 😉 

[Greg_Deckler · Accepted solution]

You can do this in a table visualization by just putting Date, Code 1, Code 2 and Code 3 in the table and SUM of Amount.

If you want a new table for it you could do:

Table = SUMMARIZE('Table',[Date],[Code 1],[Code 2],[Code 3],"Total",SUM([Amount]))

[PattemManohar · Accepted solution]

It should be a pretty straight forward....

Under Power Query Editor, use "Group By" option and use "Advanced" to add multiple columns and have the aggregated field... 

 InputOutput

[v-danhe-msft · Accepted solution]

Hi @Bwong,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Regards,

Daniel He

[PattemManohar · Accepted solution]

It should be a pretty straight forward....

Under Power Query Editor, use "Group By" option and use "Advanced" to add multiple columns and have the aggregated field... 

 InputOutput

[Greg_Deckler · Accepted solution]

You can do this in a table visualization by just putting Date, Code 1, Code 2 and Code 3 in the table and SUM of Amount.

If you want a new table for it you could do:

Table = SUMMARIZE('Table',[Date],[Code 1],[Code 2],[Code 3],"Total",SUM([Amount]))