Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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])
  )
)
v-lili6-msft's avatar
v-lili6-msft
Community Support
7 years ago

hi, Anonymous 

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

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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])
  )
)