11 Replies Latest reply on Jun 20, 2018 10:27 AM by Gerardo Varela

# Measure with a specific dimension value that ignores filters

Hi,

I am trying to get a measure with a specific dimension value that ignores the current filters. For example I want "1.57" in the network value column to appear for every Type. I'm using....

[Network Value]

if([Type] = 'network') then [Value]

ELSE

null

END

but I obviously need to be adding something. If we were to filter for Customer ZZZ instead of AAA than 5.6 should appear under Network Value for all Types. In summary I want the Network Type value of 1.57 to appear for all Network Values.

• ###### 1. Re: Measure with a specific dimension value that ignores filters

Hi Tarang,

This formula seems to be doing what you ask for:

{FIXED [Customer]:MIN(

if([Type] = 'network') then [Value]

ELSE

null

END)}

Attached workbook version 2018.1.

Regards,

Gerardo

1 of 1 people found this helpful
• ###### 2. Re: Measure with a specific dimension value that ignores filters

Awesome! It does seem to work!

Could you explain what the Min function does in this instance? I would've thought that for customer ZZZ the formula you provided would have returned 0.45 since that is the smallest value.

• ###### 3. Re: Measure with a specific dimension value that ignores filters

I'll try my best! Let's break it down in English:

At the level customer, if the type is network, return the minimum value.

As for your question, ZZZ at .45 doesn't meet the second criteria. The Type would be desktop. When building LOD's or any calculation break the formula into smaller chunks.  That way you can see what is happening. Like i did in the attached.  Let me know if that makes sense or doesn't!

Regards,

Gerardo

1 of 1 people found this helpful
• ###### 4. Re: Measure with a specific dimension value that ignores filters

This is awesome. I attached a similar Tableau file where I am trying to accomplish the same thing. In this instance for customer AAA "Network Optimized  Units" should pull the Optimized Units for Network for all Types (0.59) and for customer ZZZ (0.84).

• ###### 5. Re: Measure with a specific dimension value that ignores filters

Hi Tarang,

I made a copy of Optimized Units and changed it to:

{ FIXED [Customer],[Type]:

1+((sum([Cost])-sum([Sum TKNS]))/sum([Sum TKNS]))

}

I then changed your Network Optimized units to:

{fixed [Customer]:min([ Optimized Units (copy 2)]

)}

The results are in the attached.  Let me know if you have any questions.

Regards,

Gerardo

1 of 1 people found this helpful
• ###### 6. Re: Measure with a specific dimension value that ignores filters

How does "Network Optimized Unites" know to take the Network Type?

• ###### 7. Re: Measure with a specific dimension value that ignores filters

It doesn't!  I just saw a pattern and went for it.

If you need to you could specify it with an IF Statement like we did above.

Regards,

Gerardo

• ###### 8. Re: Measure with a specific dimension value that ignores filters

Thank you Gerardo! I added to the IF statement with your last response and it worked!

What if we wanted a combo of Desktop and Network? For example, right now "Network Optimized Units" is pulling the 1.91 from the second table. In this instance I would want 1.63 for "Network Optimized Units" for Customer AAA and 1.16 for Customer ZZZ.

My hunch is it has to do with  the "1+((sum([Cost])-sum([Sum TKNS]))/sum([Sum TKNS]))", but I am not too sure.

• ###### 9. Re: Measure with a specific dimension value that ignores filters

Morning Tarang,

You've lost me! Your screenshots and the workbook you provided don't match up.  I'm going to go by my previous workbook, sheet Grand Total, and assume we want these number since they match the location of your screen shots:

If that isn't the case please upload a workbook that matches your screen shots and I'll go it another go.

Regards,

Gerardo

• ###### 10. Re: Measure with a specific dimension value that ignores filters

Morning Gerardo!

That's super odd! I've attached the new workbook. I think it may be best to use this new file because I added a third "type".

Note it should match once "Prep" Type 1 is unchecked in the Grand Total_all tab.

Thanks!

• ###### 11. Re: Measure with a specific dimension value that ignores filters

Here's a plausible solution.  See attached.

Regards,

Gerardo

1 of 1 people found this helpful