Forum Discussion

spoony's avatar
spoony
Helper I
9 years ago
Solved

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")

Need Help
  • OwenAuger's avatar
    OwenAuger
    9 years ago

    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

     

     

13 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    9 years ago

    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

     

     

  • GilbertQ's avatar
    GilbertQ
    Super User
    9 years ago

    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.

    • spoony's avatar
      spoony
      Helper I
      9 years ago

      Hi GilbertQ, 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?