Dynamic charting issue

[Mer333]

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:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Brands
[/TD]
[TD]Categories
[/TD]
[TD]Subcategories
[/TD]
[/TR]
[TR]
[TD]Apple
[/TD]
[TD]Notebooks
[/TD]
[TD]1 edition
[/TD]
[/TR]
[TR]
[TD]Microsoft
[/TD]
[TD]Notebooks
[/TD]
[TD]2 edition
[/TD]
[/TR]
[TR]
[TD]Apple
[/TD]
[TD]Software
[/TD]
[TD]1 edition
[/TD]
[/TR]
[TR]
[TD]Microsoft
[/TD]
[TD]Software
[/TD]
[TD]2 edition
[/TD]
[/TR]
</tbody>[/TABLE]









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?

 

[Mer333]

The problem is that there are blanks in the chart's legend.

 

[ImkeF]

Hi Mer333,
unfortunately I cannot see a way around a macro here:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim cell As Range
    Application.ScreenUpdating = False
    For Each cell In Worksheets("HideLogic").Range("D13:O13")
        cell.EntireColumn.Hidden = cell.Value = 0
    Next cell
    Application.ScreenUpdating = True
End Sub

This will hide all empty columns everytime you change the slicer-selections (and thereby changing the connected pivottable) and then the blanks in the legends will disappear (you need to change the IFERROR-Value from "" to 0).

Will put that file in the blogpost as well.

It's really frustrating, that Excel charts have no way to dynamically adjust the number of colums (data series). It's possible with rows (Axis entries) (although quite laborious).
Maybe with the next realease? (Hope springs eternal )

 

[newuser1234]

Hi ImkeF

Thanks very much for taking the time to reply to my question.

The example helped me gained a lot of insight into cube functions! It is actually doing exactly what I want. However, instead of using cube I wanted to do it in Pivot table.

I want a pivot and with a slicer and the user can select which category (rows) they want to view by. Is that possible at all? thanks.

p.s im still quite fresh to powerpivot so still learning..

 

[ImkeF]

The only way I can think of is macro, but this is not my area of expertise.
Good luck

 

[scottsen]

You all inspired me.

Dynamically changing chart axis | Power Pivot | Tiny Lizard

 

[ImkeF]

Thanks Scott,
this is a triple A: Absolutely Awesome (Although not mine)
Love it!
Imke

 

[Mer333]

You all inspired me.

Dynamically changing chart axis | Power Pivot | Tiny Lizard

OMG!!! So pretty clever though quite simple... I will definitely work with this technique and see how it works with real examples and what is the possible pitfalls.

This technique opens a bunch of possibilities to implement that approach not only in excel tables but also in Power View and Power Designer dashboards...

 

[ImkeF]

Definitely - really exiting, isn't it?

But how about an even more remote usecase: Imagine a macro specialist, who had built some very nice interactive dashboards for standard excel and is now facing the challenge to publish them in Excel Services (Sharepoint, Office 365) where macros don’t run.

WDYT?: Wouldn’t this technique be worth the efford? I mean, if you are smart enough to program macros like this, at least in my eyes the new steps to learn for this technique seem doable.