Dynamic charting issue

Mer333

Board Regular
Joined
Jun 28, 2014
Messages
54
Hello everyone!

I have a problem considering that technique - Dynamic Charting In Power Pivot « PowerPivotPro

When I create a hierarchy in PP it contains duplicated names on the lower levels due to logic below:

Brands
Categories
Subcategories
Apple
Notebooks
1 edition
Microsoft
Notebooks
2 edition
Apple
Software
1 edition
Microsoft
Software
2 edition

<tbody>
</tbody>









So therefore it produces a problem after switching. For instance, when I switch a slicer to Categories it contains duplicated categories - in this example all four above, twice notebooks, twice software. But I expect to get only unique names and sums for them.

Any ideas how to solve it?
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
The answer is not to create a slicer from the hierarchy you've created. Instead create a hierarchy from the Categories field, which will only display the distinct values in the Categories column. The same goes for the MDX you're using - if you get all the members from the Categories level of the hierarchy you've created you'll get duplicate names, but if you get distinct categories you need to get the members from the Categories hierarchy (which should only have Categories on it, no Brands or Subcategories) something like this: [MyTable].[Categories].[Categories].MEMBERS

Chris
 
Upvote 0
The problem is if I have a set with different hierarchies it return an error: "Cannot handle a set that changes dimensionality."

Original set:

IIF([Measures].[CategorySelectionForecast]="Categories",
.[Classification].[Categories],
IIF([Measures].[CategorySelectionForecast]="Groups",
.[Classification].[Groups],
IIF([Measures].[CategorySelectionForecast]="Subgroups",
.[Classification].[Subgroups],
.[Classification].[Brands]))))

Set with error:

IIF([Measures].[CategorySelectionForecast]="Categories",
.[Categories].[Categories],
IIF([Measures].[CategorySelectionForecast]="Groups",
.[Groups].[Groups],
IIF([Measures].[CategorySelectionForecast]="Subgroups",
.[Subgroups].[Subgroups],
.[Brands].[Brands]))))

With slicer itself I have no problems because it is disconnected slicer that looks like:
Brands
Categories
Groups
Subgroups

I meant that after I switch it to Categories I receive duplicated categories in a set.
 
Upvote 0
What type of calculation are you trying to do here? There may be another way of writing it that doesn't need an IIF().
 
Upvote 0
Ah, ok. I see what the problem is now - and the bad news is that I don't think there's a solution if you use this particular technique. I've tried a few ideas (eg creating a set that contains tuples containing members from all hierarchies) but nothing works; I'll keep thinking about it though. You'll have to use a different method to switch what you're displaying in your PivotTable/PivotChart and not a named set.
 
Upvote 0
Unfortunately I don't know any other similar technique for dynamic rows controlled by slicer. If you know something it would be good.
Hope you'll find a solution
though.
 
Upvote 0
Hello

I've spent 3 nights looking for a solution on the net without success..essentially what i want to do is to change the rows in pivot table using the slicer which i think is what Mer333 trying to do?

Anyone out there has a solution to his please? I've tried many things and losing sleep over this....
 
Upvote 0
Hi there,

@Mer333: You might try this approach: Dynamische Charts in Excel - lets you add any attribute to your Legend (or Axis) selection without showing duplicates, crossfilter-effect should do the hierarchy.

@newuser1234: If you just want change the rows/columns without further reducing them, this is an easier approach: Dynamische Charts in Excel | Blog | sqlXpert GmbH

Sorry, our blog is in German - let me know if you need any help with the automated translation. The files with the examples might help as well.

Let me know if it worked, Imke
 
Upvote 0
Hi Imke!

WoW! I forefeel there is something that's definately game-changing.

It would be great if you translate all these articles into English.
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top