cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rsbin
Post Partisan
Post Partisan

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

 

rsbin_0-1594408225669.png

 

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.

rsbin_1-1594408225683.png

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!

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

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

Capture4.JPG

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.

View solution in original post

4 REPLIES 4
v-juanli-msft
Community Support
Community Support

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

Capture4.JPG

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.

View solution in original post

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

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
Post Partisan
Post Partisan

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!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

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