Adding back a filter when all filters have been removed

[Racq]

Hi,

I have calculated the budgeted Sales (4) for venue, food and beverage. I have page slicers for Department (Dept) and Sub Department (SubDept) so I have removed these filters too.

My budgeted sales value is correct. What I want to now do is only show the sales amount if 2 departments are selected but not if any of the other departments are selected.

The calculation for budgeted Sales is:

CALCULATE(

CALCULATE(

-SUM(pssSNAPGCCECBudgetDetail[Budget]),

NewGLCode[Parent] IN {"Venue Hire"})

+

CALCULATE(

-SUM(pssSNAPGCCECBudgetDetail[Budget]),

NewGLCode[Parent] IN {"Food"})

+

CALCULATE(

-SUM(pssSNAPGCCECBudgetDetail[Budget]),

NewGLCode[Parent] IN {"Beverage"}),

ALL(NewGLCode[Dept]),

ALL(NewGLCode[Sub Dept]),

NewGLCode[Account] IN {"4"}

)

The departments are AV, Finance, HR, IT, Operations, Prop Ops, SME.

I would like to be able to show the budgeted sales amount if Operations or Prop Ops are selected but not any of the other departments.

Thank you

[CNENFRNL]

Hi,  @Racq , it seems the measure can't be authored in a more readable manner,

=
CALCULATE (
    SUM ( pssSNAPGCCECBudgetDetail[Budget] ),
    NewGLCode[Parent] IN { "Venue Hire", "Food", "Beverage" },
    ALL ( NewGLCode[Dept], NewGLCode[Sub Dept] ),
    NewGLCode[Account] = "4",
    ALLSELECTED ( Department[Dept] )
)

 It's only a suggestion from the perspective of conciseness as it's hard to author a correct measure in one shot without a close look at the relationships in the data model.

[Racq]

Hi @CNENFRNL 

Thank you, I have updated the concise formula.

The part of the formula for ALLSELECTED ( Department[Dept]) should read ALLSELECTED ( NewGLCode[Dept]).

Which means that the formula does not work.

I am stuck on what information you need that will help with finding a solution. It all seems to focus around the NewGLCode[Dept]. I need to unfilter this slicer so that I can get the sales, but then apply the filter again to only get the sales if one of the two departments are selected.

The Dashboard I want to show is the budget for wages for the month. But only Operations and Prop Ops wages are to be shown as a %age of the sales.

Does the below screenshot help? The budget VFB column below should be 1,206,567 if the NewGLCode[Dept] slicer Operations or Prop Ops are selected, and be blank if not.

The relationships are 

[CNENFRNL]

@Racq , you might want to tweak the measure this way,

=
IF (
    ISFILTERED ( NewGLCode[Dept] ),
    CALCULATE (
        SUM ( pssSNAPGCCECBudgetDetail[Budget] ),
        NewGLCode[Parent] IN { "Venue Hire", "Food", "Beverage" },
        ALL ( NewGLCode[Dept], NewGLCode[Sub Dept] ),
        NewGLCode[Account] = "4",
        ALLSELECTED ( Department[Dept] )
    )
)

[Racq · Accepted solution]

@CNENFRNL , I managed to get the result by adding a second measure.

I used your original answer and then added a SWITCH calculation:

Var Selection = SELECTEDVALUE(NewGLCode[Dept],"ALL")

return

SWITCH(TRUE(),

Selection = "Operations", [Budget VFB],

Selection = "Prop Ops", [Budget VFB],

"")

This measure then allowed me to only show the sales value if Operations and Prop Ops department slicer was selected.

You have been very helpful.

Thanks