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],...
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

     

     

GilbertQ's avatar
GilbertQ
Super User
9 years ago

Hi spoony

 

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

 

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?

 

 

 

spoony's avatar
spoony
Helper I
9 years ago

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

 

BillsEmonthTYPECosts
GroceriesMayA300
GroceriesMayB100
  • GilbertQ's avatar
    GilbertQ
    Super User
    9 years ago

    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"
)
    • spoony's avatar
      spoony
      Helper I
      9 years ago

      Thanks GilbertQ

       

      Its almost working, i try to use it on the month and its not showing the correct totals for each type or Bills though:

       

      Table = 
CALCULATETABLE (
    ADDCOLUMNS (
        SUMMARIZE (
            'Product',
            'Product'[Emonth],
            'Product'[Bills],
            'Product'[Type]
        ),
        "Costs", CALCULATE(SUM('Product'[Costs] ),'Product'[Emonth] = "May" || 'Product'[Emonth] = "June")
    )
,
'Product'[Emonth] = "May" || 'Product'[Emonth] = "June"
)
      • GilbertQ's avatar
        GilbertQ
        Super User
        9 years ago

        Hi there

         

        In order to get it working the way that you want, you do not want to place the filter on your calculation. But you rather want to place the filter on your Table you are creating.

         

        The code below will get you the data for just the Months of May and June

         

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