cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

@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

 

View solution in original post

4 REPLIES 4
Super User III
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.

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.

Support for Question on Filters.jpg

The relationships are 

Relationships.jpg

@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] )
    )
)

@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

 

View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors