5 Replies Latest reply on Nov 18, 2018 9:13 AM by Adil Jamal

# Dynamic Selection of Top States

Hey Community!!

I've created a map interactive dashboard where if I select a particular state, I get the top 5 industries out of 16 industries as per their emission quantity and the rest 11 industries as 'Others'. For selecting the top 5 states, my friend Michel Caissie had previously helped me with the calculation which is something like this:

A new Calculation field named as 'Energy(Industry)

{ FIXED [Industry Sector]:[Parameter]}

Then, Top 1 calculation as

[Energy (Industry)]={MAX([Energy (Industry)])}

Top 2 as

[Energy (Industry)]={MAX(if not [Top 1] then [Energy (Industry)]end)}

Similarly, Top 5 as

[Energy (Industry)]= {MAX(if not [Top 1]and not [Top 2]and not [Top 3] and not [Top 4] then [Energy (Industry)]end)}

And finally, for 'others' i created a calculation field as 'Top 5 Ind'

if [Top 1] or [Top 2] or [Top 3] or [Top 4] or [Top 5] then [Industry Sector] else 'Others' END

The problem I'm facing is, many of the states are working fine, but states where the top 5 industries aren't the overall top 5 industries, I'm getting just one column instead of 5.

This is the default snapshot of my dashboard.

This is for Gujarat. As it's highlighted, 5 sectors plus one 'others' section is created as Gujarat is selected on the map.

This one is Andaman state. Instead of ;5 plus 1 column' it shows only 2.

• ###### 1. Re: Dynamic Selection of Top States

LOD expressions are global by default so you're actually showing each's state's contribution to the top 5 industries overall, rather than each state's top 5 industries. The quick fix is to make your state filter a context filter so that the Industries Share calculations operate on the filtered data rather than all the data. You still won't have 5 + Other categories for Andaman because there are only 3 industries there with data but it will help. See before and after images below.

before (global filter)

after (context filter)

1 of 1 people found this helpful
• ###### 2. Re: Dynamic Selection of Top States

Here is my article on CONTEXT filter and Orders of operations , good to know more about it

----->Order of Operations  (similar to your case)

Thanks,

Ritesh

• ###### 3. Re: Dynamic Selection of Top States

Hey Zach

Thanks a ton, man. Can't believe I was missing such minute yet important detail. It did help.

However, a small issue has arisen after this fix. In the 'Consumption Trend' bar chart below India map, I'd used dual axis. The line which represents '% of total' has stopped working which was earlier working. How can I fix that?

• ###### 4. Re: Dynamic Selection of Top States

Try backing out the context change until the line works again, in my version of your workbook there was no change to that view. There shouldn't be, because you just made the state selection filter a context filter for a different view. I think something got messed up by accident. See image below and workbook attached.

1 of 1 people found this helpful
• ###### 5. Re: Dynamic Selection of Top States

It's because you have applied 'Filter Context' in Industries Share worksheet (that's why it's showing 5 industries, which is correct as well).

Now if you look at 'Consumption Trend' chart at the bottom left, they still show only 2 coloumns because 'Filter Context' wasn't applied in that worksheet.

When I applied the context filter in that worksheet (consumption trend) I started getting 5 industries stacked chart but the dual axis line graph becomes static.