Costs Remain for Unknown Filters/Slicers (want to show zero)

[stbrooks13]

I have "unpursued costs" (the amount of money we spent attempting to pursue a candidate, but did not end up pursuing) for which I don't have filter/slicer information for. Source, gender and diversity are unknown for these costs because we did not get to the point in the recruiting process to know/track these fields. When these unknown filters/slicers are selected, the unpursued costs remain as is, when I would like for them to appear as 0. How should I write these formulas so that they appear as zero when unknown filters/slicers are selected? Note that "unknown" is not an option as a selection for the filters/slicers AND the unpursued costs are on a different table. Thank you for your help

[parry2k]

Hey, can you post sample data and expected output, it wil help to provide the solution. 

[stbrooks13]

Sure! See below image, appears without any filters/slicers selected. The figures in question are those in the bottom left corner; Other Expenses, Other Giveaways, LC Unpursued. 

However, when any of the following filters/slicers are selected, these figures remain as is; Source, Gender, Diversity (visuals in the top row shown). Below you'll see that the three formulas mentioned above remain the same when OCI is selected for the Source. However, these formulas do not have a Source (nor Gender/Diversity) associated with them, because they are unknown AND on a different table, so they should show as zero.

 

[parry2k]

Just to clarify, you are saying when click on any pie in "offer selected by source", it is filtering "FY15-FY17 Cost Per Hire Detail" but whereas you are saying it should show Zero, let me know if this is correct understanding.

You need to share what tables/values your visuals are using and seems like there is more than 1 Table and how those tables are related to each other? Also if you have any calculate measures, please share that as well. 

[stbrooks13]

It is correctly filtering the relevant formulas in the left column of the FY15-FY17 Cost per Hire Detail, but not the top three in the right column. The three on the right do not change when any piece of the pie in "Offer Accepted by Source", "Offer Accepted by Gender" and "Offer Accepted by Diversity" is selected, whereas I'd like for them to show zero.

Formulas:

Other Expenses = CALCULATE(SUM(GL[Actual]),GL[Cam/Exp]="Campus",GL[School Level]="Associations")+CALCULATE(SUM(GL[Actual]),GL[Cam/Exp]="Campus",GL[School Level]="General")+CALCULATE(SUM(GL[Actual]),GL[Cam/Exp]="Campus",GL[School Level]="Incorrect")+CALCULATE(SUM(GL[Actual]),GL[Cam/Exp]="Campus",GL[School Level]="Office Visits")+CALCULATE(SUM(GL[Actual]),GL[Cam/Exp]="Campus",GL[School Level]="Unknown")+0

Other Giveaways = IF(AND(NOT(OR('Giveaway %'[School]="Launch",'Giveaway %'[School]="Office Visits")),'Giveaway %'[Pursued]=0),CALCULATE(SUM(GL[Actual]),GL[GL Abbreviation]="Giveaways",ALLEXCEPT(GL,FY[FY (Start Date)]))*'Giveaway %'[Giveaway %])+0

LC Unpursued = CALCULATE(SUM(LC[LC Cost]),LC[Pursued]=FALSE)+0

The tables used in the formulas in question are GL, Giveaway %, FY, LC and Activity. Source, Gender and Diversity of the pursued candidates are in the Activity Table. The primary relation between all tables is FY (Fiscal Year), followed by School. If there are no candidates on the Activity table (thereby no PURSUED candidates), but there are costs associated with Expenses, Giveaways and LC, then those costs are considered "unpursued", and do not have Source, Gender and Diversity associated with them. They need to be shown overall, but  should not be shown when an unrelated filter (such as Source, Gender and Diversity) are selected from the pie charts.

[parry2k]

Thanks for clarifying, i think getting to the point where I understood the issue. So top 3 pie charts and not filtering each other when you are selecting any one of those? 

What is the data source for 3 pie charts? Same table or another table? Are those pie charts using any calculated measures?

[stbrooks13]

No, the top three pie charts are not associated with the top three formulas in the FY15-FY17 Cost per Hire Detail, they are remaining as is, when I need them to show zero.

Source, Gender and Diversity of the pursued candidates are in the Activity Table using the following calculated column:  Offers Accepted = CALCULATE(COUNT(Activity[Offer Accepted]),Activity[Offer Accepted]>0)+0

Other Expenses is in the GL table.

Other Giveaways is in the GL AND Giveaway % tables.

LC Unpursued is in the GL AND LC tables.

[v-ljerr-msft · Accepted solution]

Hi @stbrooks13,

If I understand you correctly, you should be able to use the formulas below to create new measures for [Other Expenses], [Other Giveaways], [LC Unpursued], then show the new created measures on the report to get your expected result. 

New Other Expenses =
IF (
    HASONEFILTER ( Activity[Source] ) || HASONEFILTER ( Activity[Gender] )
        || HASONEFILTER ( Activity[Diversity] ),
    0,
    [Other Expenses]
)

New Other Giveaways =
IF (
    HASONEFILTER ( Activity[Source] ) || HASONEFILTER ( Activity[Gender] )
        || HASONEFILTER ( Activity[Diversity] ),
    0,
    [Other Giveaways]
)

New LC Unpursued =
IF (
    HASONEFILTER ( Activity[Source] ) || HASONEFILTER ( Activity[Gender] )
        || HASONEFILTER ( Activity[Diversity] ),
    0,
    [LC Unpursued]
)

Regards

[stbrooks13]

Works perfectly, thank you! I also wasn't aware that I could use ||, so that's helpful 🙂