8 Replies Latest reply on Jun 23, 2016 6:54 AM by Ray Yu

Dynamic Grouping

Hi,

I have a question regarding dynamic grouping.

I have company A,B,C,D. I want to create a graph that, when I choose Company A, it will automatically group Company B,C,D as Others; when I choose Company B, it will group Company A,C,D as Others; and so on. Does Anyone have any great ideas how to achieve this?

Thanks

Ray

• 1. Re: Dynamic Grouping

Hey Ray,

Do this with a Parameter named "selected parameter" that allows the user to choose between the companies.

Then, you can simply do this for your dimension:

IF [Selected Company] = [Company] THEN [Company] ELSE 'OTHER' END

• 2. Re: Dynamic Grouping

Hi Ray,

If you need to select more than one company to compare against the 'Others'  and your ok with row labels using measure names like 'selected' and 'other' you can build measures that won't require a parameter.  Check out the attachment and let me know if you have any questions.

Regards,
Ivan

1 of 1 people found this helpful
• 3. Re: Dynamic Grouping

Hi Ivan,

Thanks for your help. I think your solution is great and simple. If I also want to show the percentage of total of the selected company, do you have any suggestions?

Thanks,

Ray

• 4. Re: Dynamic Grouping

Thanks Derrick! This works for me!

• 5. Re: Dynamic Grouping

Hi Ray, You could calculate % of total for selected with SUM([Sales])/MIN({FIXED : SUM([Sales]) }) and for other with (MIN({FIXED:SUM( [Sales] )})-SUM([Sales]))/MIN({FIXED:SUM( [Sales] )})

• 6. Re: Dynamic Grouping

Hi Ivan,

I would like to create a bar graph like this:

I guess the multiple measures method won't work on this. I was able to achieve this using parameters.

Thanks anyway for your help. It was a good learning.

Thanks,

Ray

• 7. Re: Dynamic Grouping

You can't make a true stacked bar but you can achieve a similar look with a hack.  Take a look and see if you think it's worth pursuing.

• 8. Re: Dynamic Grouping

Thanks Ivan, this is a very clever workaround.