cancel
Showing results for
Did you mean:
New Member

## Adding back a filter when all filters have been removed

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

1 ACCEPTED SOLUTION
New Member

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

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.

Thanks

4 REPLIES 4
Super User III

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.

New Member

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

Super User III

@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] )
)
)``````
New Member

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

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.

Thanks

Announcements