Forum Discussion

Applicable88's avatar
Applicable88
Icon for Impactful Individual rankImpactful Individual
5 years ago

Where to put removefilters within a summarize function?

Hello,

I have a typical summarize function inside a addcolumns and Sumx function. That filter part should not react to filters of a column called DAYS. Where to put the Removefilters() inside that setting?

I tried put REMOVEFILTERS('Table1'[DAYS]) behind the Calculate average part. It does not filter correctly. I think Removefilters should already filter the Data in the filter part inside the summarzie function.

 

SUMX(

ADDCOLUMNS(

SUMMARIZE(

FILTER('Table1,

'Table1'[Month] = EDATE(MIN('momentarilyl'[Devices]),VALUE1) &&

'Table1f'[Department] = "Security" &&

'Table1'[Category] in {"Orders","Planned"} &&

'Table1'[Status] in {"progressing","open"}),

'Table1'[Orders]),

"AVG",

CALCULATE(

AVERAGE('Table1'[Sales]),

[AVG]))

 

Thank you very much in advance.

Best. 

Need Help
Tips and Tricks
Tutorial Requests

8 Replies

    • Applicable88's avatar
      Applicable88
      Icon for Impactful Individual rankImpactful Individual
      5 years ago

      Sorry, I forgot to input the name of the measure. Now the syntax is right:

       

      Measure =
SUMX (
    ADDCOLUMNS (
        SUMMARIZE (
            FILTER (
                'Table1',
                'Table1'[Month] = EDATE ( MIN ( 'momentarilyl'[Devices] ), VALUE1 )
                    && 'Table1f'[Department] = "Security"
                    && 'Table1'[Category]
                    IN { "Orders", "Planned" }
                    && 'Table1'[Status] IN { "progressing", "open" }
            ),
            'Table1'[Orders]
        ),
        "AVG", CALCULATE ( AVERAGE ( 'Table1'[Sales] ) )
    ),
    [AVG]
)

       

      Measure =
      SUMX (
      ADDCOLUMNS (
      SUMMARIZE (
      FILTER (
      'Table1',
      'Table1'[Month] = EDATE ( MIN ( 'momentarilyl'[Devices] ), VALUE1 )
      && 'Table1f'[Department] = "Security"
      && 'Table1'[Category]
      IN { "Orders", "Planned" }
      && 'Table1'[Status] IN { "progressing", "open" }
      ),
      'Table1'[Orders]
      ),
      "AVG", CALCULATE ( AVERAGE ( 'Table1'[Sales] ) )
      ),
      [AVG]
      )

      • Icey's avatar
        Icey
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi Applicable88 ,

         

        How about this?

        Measure =
SUMX (
    ADDCOLUMNS (
        CALCULATETABLE (
            SUMMARIZE (
                FILTER (
                    'Table1',
                    'Table1'[Month] = EDATE ( MIN ( 'momentarilyl'[Devices] ), VALUE1 )
                        && 'Table1f'[Department] = "Security"
                        && 'Table1'[Category]
                        IN { "Orders", "Planned" }
                        && 'Table1'[Status] IN { "progressing", "open" }
                ),
                'Table1'[Orders]
            ),
            REMOVEFILTERS ( 'Table1'[DAYS] )
        ),
        "AVG", CALCULATE ( AVERAGE ( 'Table1'[Sales] ) )
    ),
    [AVG]
)

        Measure =
        SUMX (
        ADDCOLUMNS (
        CALCULATETABLE (
        SUMMARIZE (
        FILTER (
        'Table1',
        'Table1'[Month] = EDATE ( MIN ( 'momentarilyl'[Devices] ), VALUE1 )
        && 'Table1f'[Department] = "Security"
        && 'Table1'[Category]
        IN { "Orders", "Planned" }
        && 'Table1'[Status] IN { "progressing", "open" }
        ),
        'Table1'[Orders]
        ),
        REMOVEFILTERS ( 'Table1'[DAYS] )
        ),
        "AVG", CALCULATE ( AVERAGE ( 'Table1'[Sales] ) )
        ),
        [AVG]
        )

         

         

        Best Regards,

        Icey

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    5 years ago

    Hi,

    Please share some data, describe the question and show the expected result.

    • Applicable88's avatar
      Applicable88
      Icon for Impactful Individual rankImpactful Individual
      5 years ago

      Ashish_Mathur , sorry I have no sample data. In general the above calculatio works perfectly. The only thing I need to add is that it disregard selections or filtering of the Column 'Days'.

       

      I tried the example from Icey  and add the removefilters() with a calculatetable filter:

      Measure =
SUMX (
ADDCOLUMNS (
CALCULATETABLE (
SUMMARIZE (
FILTER (
'Table1',
'Table1'[Month] = EDATE ( MIN ( 'momentarilyl'[Devices] ), VALUE1 )
&& 'Table1f'[Department] = "Security"
&& 'Table1'[Category]
IN { "Orders", "Planned" }
&& 'Table1'[Status] IN { "progressing", "open" }
),
'Table1'[Orders]
),
REMOVEFILTERS ( 'Table1'[DAYS] )
),
"AVG", CALCULATE ( AVERAGE ( 'Table1'[Sales] ) )
),
[AVG]
)

       But unfortunately whenever I filter something in the "Days" Slicer it still filters the Data accordingly which I want it to ignore. 

      Maybe you have an idea?

      Best.