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

     

     

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

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

    Ah everything is working now, Thanks heaps!!

     

    Now i can justify my $20/hr job !!

  • GilbertQ's avatar
    GilbertQ
    Super User
    9 years ago

    Glad to be of assistance... Kudos!