Forum Discussion

rsbin's avatar
rsbin
Icon for Community Champion rankCommunity 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.
rsbin's avatar
rsbin
Icon for Community Champion rankCommunity 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!