Forum Discussion
SummarizeColumns with Multiple Filters
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
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.
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?
Hi, I just created some sample data:
Bills Emonth Type Costs Groceries January A 200 Groceries May A 200 Groceries February A 200 Groceries March A 200 Groceries April A 200 Groceries May A 100 Groceries June A 100 Groceries July A 100 Groceries August A 100 Groceries September A 100 Groceries October A 100 Groceries November A 100 Groceries December A 100 Groceries January B 100 Groceries February B 100 Groceries March B 100 Groceries April B 100 Groceries May B 100 Other June B 100 Other July B 100 Other August B 100 Other September B 100 Other October B 100 Other November B 100 Other December B 100 The result should be:
Bills Emonth Costs Groceries May 400
