Forum Discussion

rsbin's avatar
rsbin
Community Champion
6 years ago
Solved

Calculating Benchmark - ALLEXCEPT not quite working

Good Afternoon,

 

I have the following measure:  Ratio = EquipIDCount / TotalVisits.  This is represented by the blue line for each date.

My page filter contexts are Date and Facility.   I need to calculate a Benchmark across all Facilities.

I have calculated the red line using the following measure which removes all filter contexts (

Facility and Date).

 

Benchmark = Calculate( 'Work Item Measures'[EquipmentIDCount]/[TotalVisits], ALL(GeneralStatistics))

 

 

However, I want the Benchmark to retain the Date filter context and calculate the Benchmark across the date range selected.

 

If I use the AllExcept function with the Date filter,

 

Benchmark = Calculate( 'Work Item Measures'[EquipmentIDCount]/[TotalVisits], ALLEXCEPT('Calendar','Calendar'[Date]))

 

I simply get a repeat of the original measure where it is doing the calculation on each date and superimposing both measures.

Hoping someone can recommend how I would modify my Benchmark Measure to obtain a straight line calculation across the entire time period selected and not for each specific date.

 

Thanks in advance and good weekend!

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi rsbin 

    Create a date table which has no relationship with fact table,

    create a measure

    Measure 2 = CALCULATE( [idcount]/[visit],FILTER(ALL('Table 3'),'Table 3'[date]>=MIN('date'[Date])&&'Table 3'[date]<=MAX('date'[Date])))

    slicer "facility" won't change the value of [Measure 2], the date slicer would change the [Measure 2].

     

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

4 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi rsbin 

    Create a date table which has no relationship with fact table,

    create a measure

    Measure 2 = CALCULATE( [idcount]/[visit],FILTER(ALL('Table 3'),'Table 3'[date]>=MIN('date'[Date])&&'Table 3'[date]<=MAX('date'[Date])))

    slicer "facility" won't change the value of [Measure 2], the date slicer would change the [Measure 2].

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    • rsbin's avatar
      rsbin
      Community Champion
      6 years ago

      Hello v-juanli-msft 

      Thank you for the response.  Over the weekend I was able to come up with a very similar solution.

      First, I realized I was going down the wrong path with my SUMX solution above.

       

      By trial and error, I realized I can use the Analytics Pane, to insert the following measure:

      Benchmark = CALCULATE([EquipmentIDCount]/[TotalVisits], ALL(GeneralStatistics), 
            DATESBETWEEN( 'Calendar'[Date],MIN('Calendar'[Date]), MAX('Calendar'[Date])))

      It looks like this is acting the same as way as your independent Date Table is doing.

      Thank you much for taking the time to reply.

       

      Best Regards,

    • rsbin's avatar
      rsbin
      Community Champion
      6 years ago

      v-juanli-msft 

      Hi Maggie,

      I am trying better to understand your solution.  My entire data model is premised on a Fact Table and a related Calendar Table.  I currently use this Calendar Table as my Slicer throughout my multi-page report.  I have dozens of Measures that are based on the date range selected by the User.

      Introducing another unrelated Date Table as you suggested, and using that as a Slicer, would affect all the other visuals I have on my report page - would it not?

       

      Kind Regards,

  • rsbin's avatar
    rsbin
    Community Champion
    6 years ago

    So far, I have found a partial solution.   The following gives me one of the running totals that I need.

    RunningTotal_1 = SUMX(FILTER(ALLSELECTED('Calendar'[Date]),'Calendar'[Date]<=MAX('Calendar'[Date])),[EquipmentIDCount])

     However, I need to remove the Facility filter context from this formula.  I have tried different combinations of "ALL" and "ALLEXCEPT", but unable to get it to work as it should.   Any suggestions much appreciated!