cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
xarius
Helper I
Helper I

Remove filter from measure inside summarize

Hi,

 

I can't seem to get the correct combination to get the measure that I want. I'm ultimately trying to find the amount of time each team spends in each area as a percent of the total time spent by both teams in any area (see below for some dummy data and the measure I have so far), averaged over the number of events there are.

 

Where I'm coming unstuck is that the ALL function doesn't seem to be removing the filters on area and team like I thought - I would have expected [TimeNotFiltered] to have given 93.3 in all cells in the matrix below, not just the total - [TimeFiltered] and [TimeNotFiltered] are giving the same results.

 

I tried using ALLEXCEPT(Table1, Table1[Event]) which worked in the case I'm describing above, but lost the other filters that I had available which I wanted to keep (minute and period).

 

I'm not sure where I am going wrong, any help would be really appreciated.

 

 

 

Test DataTest DataMeasure issueMeasure issue

1 ACCEPTED SOLUTION

Hi @v-lili6-msft ,

 

This wasn't quite what I was looking for, but I think it may have got me to where I needed it to.

 

In the end, as I needed to have some data filtered and some not, I did a natural join of the table as filtered and as not and used that as the input table.

 

Here is my final measure for reference:

 

TimePerEvent = IF(
  HASONEVALUE(Table1[Event]),
  DIVIDE(SUM(Table1[Time]), CALCULATE(SUM(Table1[Time], ALL(Table1[Area],Table1[Team]))),
  AVERAGEX(
    NATURALINNERJOIN(
      SUMMARIZE(
        Table1,
        Table1[Event],
        "TimeFiltered", SUM(Table1[Time])
      ),
      SUMMARIZE(
        CALCULATETABLE(Table1, ALL(Table1[Area],Table1[Team])),
        Table1[Event],
        "TimeNotFiltered", SUM(Table1[Time])
      )
    ),
    DIVIDE([TimeFiltered],[TimeNotFiltered])
  )
)

View solution in original post

2 REPLIES 2
v-lili6-msft
Community Support
Community Support

hi, @xarius 

You may adjust your formula like below:

Measure = AVERAGEX(SUMMARIZE(ALLSELECTED(Table1),Table1[Event],"timefilter",SUM(Table1[Time]),"timenofilter",CALCULATE(SUM(Table1[Time]),ALL(Table1[Area],Table1[Team]))),[timefilter])

or 

Measure 2 = AVERAGEX(SUMMARIZE(ALL(Table1),Table1[Event],"timefilter",SUM(Table1[Time]),"timenofilter",CALCULATE(SUM(Table1[Time]),ALL(Table1[Area],Table1[Team]))),[timefilter])

Use ALL or ALLSELECTED in summarize table.

 

Best Regards,

Lin

 

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

Hi @v-lili6-msft ,

 

This wasn't quite what I was looking for, but I think it may have got me to where I needed it to.

 

In the end, as I needed to have some data filtered and some not, I did a natural join of the table as filtered and as not and used that as the input table.

 

Here is my final measure for reference:

 

TimePerEvent = IF(
  HASONEVALUE(Table1[Event]),
  DIVIDE(SUM(Table1[Time]), CALCULATE(SUM(Table1[Time], ALL(Table1[Area],Table1[Team]))),
  AVERAGEX(
    NATURALINNERJOIN(
      SUMMARIZE(
        Table1,
        Table1[Event],
        "TimeFiltered", SUM(Table1[Time])
      ),
      SUMMARIZE(
        CALCULATETABLE(Table1, ALL(Table1[Area],Table1[Team])),
        Table1[Event],
        "TimeNotFiltered", SUM(Table1[Time])
      )
    ),
    DIVIDE([TimeFiltered],[TimeNotFiltered])
  )
)

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors