Forum Discussion

AllanBerces's avatar
AllanBerces
Post Prodigy
1 year ago
Solved

Summarize and Filter

Hi good day, can anyone help me on how can i incorporate the filter i need on the table i summarized.   Summarized_Progress =SUMMARIZE( 'PROGRESS', 'PROGRESS'[Column1], 'PROGRESS'[Column2] , '...
Need Help
  • MNedix's avatar
    MNedix
    1 year ago

    Heya,

     

    Create a table using the FILTER function. Something like the below:

    Summarized_Progress (forum)=
FILTER(
	SUMMARIZE(
		'PROGRESS',
		'PROGRESS'[Column1],
		'PROGRESS'[Column2] ,
		'PROGRESS'[Sub] ,
		'PROGRESS'[Column4] ,
		'PROGRESS'[Week No.] ,
		'PROGRESS'[Year] ,
		'PROGRESS'[Project] ,
		'PROGRESS'[TA] ,
		"_Progress", CALCULATE(SUM('PROGRESS'[Earned])),
		[TA] <>  "Unknown"
        && [Sub]
            IN { "Trade 1", "Trade 2", "Trade 3", "Trade 4" }
                && [Year] = 2024
                && [Week No.] < WEEKNUM ( TODAY () )))

     

    If it answered your question please mark it as the solution so others can see it. 

     

    Best,

elitesmitpatel's avatar
elitesmitpatel
Solution Supplier
1 year ago

Code 1 -->
Summarized_Progress =
VAR FilteredData =
FILTER(
'PROGRESS',
[TA] <> "Unknown"
&& [Sub] IN {"Trade 1", "Trade 2", "Trade 3", "Trade 4"}
&& [Year] = 2024
&& [Week No.] < WEEKNUM(TODAY())
)
RETURN
SUMMARIZE(
FilteredData,
'PROGRESS'[Column1],
'PROGRESS'[Column2],
'PROGRESS'[Sub],
'PROGRESS'[Column4],
'PROGRESS'[Week No.],
'PROGRESS'[Year],
'PROGRESS'[Project],
'PROGRESS'[TA],
"_Progress",
CALCULATE(SUM('PROGRESS'[Earned]))
)


Code 2 -->
Summarized_Progress =
SUMMARIZE(
FILTER(
'PROGRESS',
'PROGRESS'[TA] <> "Unknown" &&
'PROGRESS'[Sub] IN { "Trade 1", "Trade 2", "Trade 3", "Trade 4" } &&
'PROGRESS'[Year] = 2024 &&
'PROGRESS'[Week No.] < WEEKNUM(TODAY())
),
'PROGRESS'[Column1],
'PROGRESS'[Column2],
'PROGRESS'[Sub],
'PROGRESS'[Column4],
'PROGRESS'[Week No.],
'PROGRESS'[Year],
'PROGRESS'[Project],
'PROGRESS'[TA],
"_Progress", CALCULATE(SUM('PROGRESS'[Earned]))
)