Prevent Slicer from impacting part of Measure

[Anonymous]

Hi, I am having problems with a visual (below) not working correctly when a slicer is applied - I have looked at related links but have found nothing that I could use.

Data tables:

Table 1: Calendar (Dates used as axis. Also contains a calculated column counting when a date is a working day) (related to Table 2 1- *)

Table 2: Financials Fact table containing example revenue data in graphic

Table 3: Account Key (this relates to Table 2 1- *) (used as slicer to filter revenue from table 2)

Problem:

I would like to track Revenue (table 2) divided by the number of working days per month(table1). I have created a measure which does this:

Run Rate_Revenue = divide

 ([Current Forecast_Revenue_sum],

 calculate(sum(

 'Calendar'[Work days]),ALLEXCEPT('Calendar','Calendar'[Date])))

I tried to use the allexcept function to negate the impact from slicer 3, however this is not working the way I had intended.

However When I use an account slicer (table 3) , the measure values do not show and something is going wrong.

Example table (no slicers applied): (Correctly showing Revenue, working days and the Run Rate measure for each year/month)

Slicer applied: (Revenue shows correctly, Work days do not show at all, Run Rate measure very wrong)

Data relationships:

Thoughts to solve much appreciated.

J

[Greg_Deckler · Accepted solution]

Would help to post sample data as text that can be copied in order to recreate. Perhaps try something like CALCULATE with REMOVEFILTERS?

[Anonymous]

Amazing - thanks Greg - the one function I hadn't yet found! Had spent all my time trying to make the slicer not apply using All, allexcept etc. I'll save this one for the future.

Really appreciate your time today, especially as you commented on both posts, I was finding it hard to explain the issue concisely.

I ended up amending the measure to the below, excluding the specific column which was acting as the slicer.

Run Rate_Revenue = divide

 ([Current Forecast_Revenue_sum],

 calculate(sum(

 'Calendar'[Work days]),REMOVEFILTERS('All DPA Allocation_SuperSector'[SS - Group])))