Reply
Highlighted
Regular Visitor
Posts: 28
Registered: ‎03-30-2017
Accepted Solution

SummarizeColumns with Multiple Filters

Is it possible to add multiple filters to SUMMARIZECOLUMNS()?

 

I tried the below and its not giving me the correct number of rows:

 

Table = SUMMARIZECOLUMNS('Product'[Emonth], 'Product'[Bills], FILTER('Product', Product'[Emonth]' = "May"), FILTER('Product', 'Product'[Bills] = "Groceries")


Accepted Solutions
Established Member
Posts: 184
Registered: ‎02-29-2016

Re: SummarizeColumns with Multiple Filters

@spoony

 

You can do something like this:

Table =
CALCULATETABLE (
    SUMMARIZECOLUMNS ( 'Product'[Emonth], 'Product'[Bills], 'Product' ),
    'Product'[Emonth] = "May",
    'Product'[Bills] = "Groceries"
)

I added 'Product' as a filter argument to SUMMARIZECOLUMNS, then wrapped in CALCULATETABLE containing the column filters.

(Since the table is calculated in an unfiltered context, I turned your FILTER functions into single column filters.)

 

Does this give the intended result?

 

Cheers,

Owen

 

 

View solution in original post


All Replies
New Contributor
Posts: 600
Registered: ‎06-24-2015

Re: SummarizeColumns with Multiple Filters

Hi @spoony

 

When creating a Calculated Table I typically use the syntax below.

 

Cash Flow Table = 
CALCULATETABLE (
    ADDCOLUMNS (
        SUMMARIZE (
            'TableName',
            'TableName'[Fiscal Year],
            'TableName'[Fiscal Date]
        ),
        "CFF", SUM ( 'TableName'[Operating Cash Flow] ),
        "Rowz", DISTINCTCOUNT ( 'TableName'[Fiscal Year] )
    ),
    'TableName'[Budget Version] = "Cash Flow",
    'TableName'[Fiscal Date]
        >= VALUES ( 'TableName'[Fiscal Date] )
)

The last section is where I have put in my filters, in which there are multiple filters.

Established Member
Posts: 184
Registered: ‎02-29-2016

Re: SummarizeColumns with Multiple Filters

@spoony

 

You can do something like this:

Table =
CALCULATETABLE (
    SUMMARIZECOLUMNS ( 'Product'[Emonth], 'Product'[Bills], 'Product' ),
    'Product'[Emonth] = "May",
    'Product'[Bills] = "Groceries"
)

I added 'Product' as a filter argument to SUMMARIZECOLUMNS, then wrapped in CALCULATETABLE containing the column filters.

(Since the table is calculated in an unfiltered context, I turned your FILTER functions into single column filters.)

 

Does this give the intended result?

 

Cheers,

Owen

 

 

Regular Visitor
Posts: 28
Registered: ‎03-30-2017

Re: SummarizeColumns with Multiple Filters

Thanks  @OwenAuger and @guavaq

 

I tried @OwenAuger method and it looks like its working now. 

Regular Visitor
Posts: 28
Registered: ‎03-30-2017

Re: SummarizeColumns with Multiple Filters

Hi @guavaq@OwenAuger

 

Im having problems getting a Sum column to work with this, can either of you help? Im using guavag's method below:

 

Table = 
CALCULATETABLE (
    ADDCOLUMNS (
        SUMMARIZE (
            'Product',
            'Product'[Emonth],
            'Product'[Bills]
        ),
        "Costs", SUM('Product'[Costs] )
    ),
    'Product'[Emonth] = "May",
'Product'[Bills] = "Groceries"
)

Its giving me total sum of everything per row without taking account of the filters or row information. 

 

 Or how do you add a Sum() in to SummarizeColumns with filters?

New Contributor
Posts: 600
Registered: ‎06-24-2015

Re: SummarizeColumns with Multiple Filters

Hi @spoony

 

Do you have any sample data to share?

Regular Visitor
Posts: 28
Registered: ‎03-30-2017

Re: SummarizeColumns with Multiple Filters

 

Hi, I just created some sample data:

 

BillsEmonthTypeCosts
GroceriesJanuaryA200
GroceriesMayA200
GroceriesFebruaryA200
GroceriesMarchA200
GroceriesAprilA200
GroceriesMayA100
GroceriesJuneA100
GroceriesJulyA100
GroceriesAugustA100
GroceriesSeptemberA100
GroceriesOctoberA100
GroceriesNovemberA100
GroceriesDecemberA100
GroceriesJanuaryB100
GroceriesFebruaryB100
GroceriesMarchB100
GroceriesAprilB100
GroceriesMayB100
OtherJuneB100
OtherJulyB100
OtherAugustB100
OtherSeptemberB100
OtherOctoberB100
OtherNovemberB100
OtherDecemberB100

 

The result should be:

 

BillsEmonthCosts
GroceriesMay400
New Contributor
Posts: 600
Registered: ‎06-24-2015

Re: SummarizeColumns with Multiple Filters

Hi @spoony

 

When I take your sample data and put it into a table as shown below.

 

Power BI - DELETE.png

I then take your exact Syntax and get the following table output, which is what you are expecting to see, based on the Filters in the CalculateTable?

 

Power BI - DELETE.png

 

 

Regular Visitor
Posts: 28
Registered: ‎03-30-2017

Re: SummarizeColumns with Multiple Filters

Hmm wat happens when you put in the Type column, Does it still show the breakdown?

 

BillsEmonthTYPECosts
GroceriesMayA300
GroceriesMayB100
New Contributor
Posts: 600
Registered: ‎06-24-2015

Re: SummarizeColumns with Multiple Filters

This will work below if you only want values for the Groceries

 

Table = CALCULATETABLE (
    ADDCOLUMNS (
        SUMMARIZE (
            'Product',
            'Product'[Emonth],
            'Product'[Bills],
			'Product'[Type]
        ),
        "Costs", CALCULATE(SUM('Product'[Costs] ),'Product'[Bills] = "Groceries")
    )//,
    //'Product'[Emonth] = "May"
   // 'Product'[Bills] = "Groceries"
)

Or this if you want the other Bills data also.

 

Table = CALCULATETABLE (
    ADDCOLUMNS (
        SUMMARIZE (
            'Product',
            'Product'[Emonth],
            'Product'[Bills],
			'Product'[Type]
        ),
        "Costs", CALCULATE(SUM('Product'[Costs] ),'Product'[Bills] = "Groceries" || 'Product'[Bills] = "Other")
    )//,
    //'Product'[Emonth] = "May"
   // 'Product'[Bills] = "Groceries"
)