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