Calculating Benchmark - ALLEXCEPT not quite working

[rsbin]

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!

[v-juanli-msft · Accepted solution]

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]

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

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]

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!