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] ,
'PROGRESS'[Sub] ,
'PROGRESS'[Column4] ,
'PROGRESS'[Week No.] ,
'PROGRESS'[Year] ,
'PROGRESS'[Project] ,
'PROGRESS'[TA] ,
"_Progress", CALCULATE(SUM('PROGRESS'[Earned])))
 
I want to filter: 
    [TA] <>  "Unknown"
        && [Sub]
            IN { "Trade 1", "Trade 2", "Trade 3", "Trade 4" }
                && [Year] = 2024
                && [Week No.] < WEEKNUM ( TODAY () )
 
Thank you
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,

3 Replies

  • MNedix's avatar
    MNedix
    Solution Sage
    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]))
    )