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

 

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