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?
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

ChrisWebb

New Member
Joined
Sep 2, 2014
Messages
27
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
 

Mer333

Board Regular
Joined
Jun 28, 2014
Messages
54
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.
 

ChrisWebb

New Member
Joined
Sep 2, 2014
Messages
27
What type of calculation are you trying to do here? There may be another way of writing it that doesn't need an IIF().
 

ChrisWebb

New Member
Joined
Sep 2, 2014
Messages
27
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.
 

Mer333

Board Regular
Joined
Jun 28, 2014
Messages
54

ADVERTISEMENT

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.
 

newuser1234

New Member
Joined
Feb 20, 2015
Messages
2
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....
 

ImkeF

Well-known Member
Joined
Feb 1, 2015
Messages
544
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
 

Mer333

Board Regular
Joined
Jun 28, 2014
Messages
54
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,341
Messages
5,528,147
Members
409,804
Latest member
aceyus_michael

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top