Pivot Exploding Doughnut Chart

[spycein]

Hi every one,

I want to create two different pivot chart inter linked with a slicer.

I have the following pivot tables and a slicer created from my source Data.


[TABLE="class: grid, width: 453"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Product Name[/TD]
[TD] Amount[/TD]
[TD][/TD]
[TD]Sales Person[/TD]
[TD] Amount[/TD]
[/TR]
[TR]
[TD]CPU[/TD]
[TD] 35,886,152[/TD]
[TD][/TD]
[TD]Ami[/TD]
[TD] 19,947,550[/TD]
[/TR]
[TR]
[TD]Keyboard[/TD]
[TD] 12,823,794[/TD]
[TD][/TD]
[TD]Andi[/TD]
[TD] 30,277,553[/TD]
[/TR]
[TR]
[TD]Monitor[/TD]
[TD] 29,450,930[/TD]
[TD][/TD]
[TD]Nick[/TD]
[TD] 33,458,416[/TD]
[/TR]
[TR]
[TD]Mouse[/TD]
[TD] 16,259,880[/TD]
[TD][/TD]
[TD]Roger[/TD]
[TD] 37,445,016[/TD]
[/TR]
[TR]
[TD]Speaker[/TD]
[TD] 26,707,779[/TD]
[TD][/TD]
[TD]Grand Total[/TD]
[TD] 121,128,535[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD] 121,128,535[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I want to create a donought chart linked with another exploding donought chart.

For example,

If select CPU in the slicer, the first donought chart would display CPU as exploding part.
And in the second chart would display the total sale of CPU sales person wise.

Similarly if I select Speaker, in the slicer, the first donought chart would display Speaker as exploding part.
And in the second chart would display the total sale of Speaker sales person wise.

Also attached herewith the screenshot of the excel sheet for your reference.

[/URL][/IMG]

Thank you so much

Shib

 

[tygrrboi]

Re: Pivot Exploding Donought Chart

Using VBA changing the amount of explosion is


ActiveChart.SeriesCollection(1).Points(1).Explosion = 50 'or any other %...

Change the series number and the point number based on what is selected. Probably in a worksheet event if the selection happens in a cell.

 

[tygrrboi]

Re: Pivot Exploding Donought Chart

On your sheet I have a dropdown menu in G15 with all the options from your chart. Then in G16 I have =MATCH(G15,A3:A7,0) -change the blue range to wherever you have the ProductNames stored... so when CPU is selected in G15, G16 says 1...

Then in the sheet section I put the following worksheet_change event.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then GoTo Quit
If Intersect(Target, Range("G15")) Is Nothing Then GoTo Quit
Dim i As Integer, p As Integer
i = Target.Offset(, 1).Value


With ChartObjects(1).Chart
[COLOR=#ff0000]'    Select Case 1[/COLOR]
[COLOR=#ff0000]'    Case Is = 1[/COLOR]
[COLOR=#ff0000]'        .ChartGroups(1).FirstSliceAngle = 0[/COLOR]
[COLOR=#ff0000]'    Case Else[/COLOR]
[COLOR=#ff0000]'        .ChartGroups(1).FirstSliceAngle = 0[/COLOR]
[COLOR=#ff0000]'    End Select[/COLOR]
    
    For p = 1 To .SeriesCollection(1).Points.Count  [COLOR=#008000] 'resets all pieces back to 0[/COLOR]
         .SeriesCollection(1).Points(p).Explosion = 0
    Next p
    
     .SeriesCollection(1).Points(i).Explosion = 10 [COLOR=#008000] 'pops out the desired piece[/COLOR]
End With
Quit:
End Sub

Ignore the red bit I am trying to figure out how you would rotate the chart so that the exploded pie piece is always exploding out 90 degrees (or to the right)... But I am not very good at geometry...

 

[tygrrboi]

Re: Pivot Exploding Donought Chart

Finally got it. Even though you did not ask for it... I thought it might make a cool effect. So I will include it as an option for you. This will rotate the chart so that the piece in focus will always be on the right (as well as exploded out).

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then GoTo Quit
If Intersect(Target, Range("G15")) Is Nothing Then GoTo Quit


Dim i As Integer, p As Integer
Dim dSum As Double
Dim cell As Range


i = Target.Offset(, 1).Value


With ChartObjects(1).Chart
    Select Case i
    Case Is = 1 [COLOR=#008000]'the first item in the list.. in this case its CPU[/COLOR]
        .ChartGroups(1).FirstSliceAngle = Int(90 - (0.5 * (Range("B3").Value / Range("B8").Value * 360)))  
[COLOR=#008000]                                           'B3 is where I have the Amount number of CPU stored. B8 is where I have the grand total stored... change accordingly...    [/COLOR]
    Case Else
        For Each cell In Range("B" & i + 2, "B7")
[COLOR=#008000]                       'B(i + 2) represents B4, B5, B6, or B7 depending on the selection.. These are where I have the Amount numbers of the other list options stored. _ 
                       B7 is where I have the last item's amount stored.     Please adjust accordingly.  But keep it in this format.     [/COLOR]
            dSum = dSum + (cell.Value / Range("B8").Value * 360)
[COLOR=#008000]                       '[/COLOR][COLOR=#008000]B8 is where I have the grand total stored.     Please adjust accordingly. [/COLOR]
        Next cell
        .ChartGroups(1).FirstSliceAngle = Int(dSum + (90 - (0.5 * (Range("B" & i + 2).Value / Range("B8").Value * 360))))
[COLOR=#008000]                                 'B(i + 2) represents B4, B5, B6, or B7 depending on the selection.. B8[/COLOR][COLOR=#008000] is where I have the grand total stored.  Please adjust accordingly.  But keep it in this format.     [/COLOR]
    End Select
    
    For p = 1 To .SeriesCollection(1).Points.Count
         .SeriesCollection(1).Points(p).Explosion = 0
    Next p
    
     .SeriesCollection(1).Points(i).Explosion = 10
End With
Quit:
End Sub

 

[spycein]

Re: Pivot Exploding Donought Chart

Thank You So Much tygrrboi for your help.

Actually i am new in excel VBA, Could you please share the excel file so that i can refer.

Thanks

 

[tygrrboi]

Re: Pivot Exploding Donought Chart

A quick note. before I do... In VBA (unlike on a worksheet) When you move data on the sheet, the VBA code does not automatically adjust. So basically you can not simply take my file and move the columns and such and expect to have the results updated automatically.

I will share my file so you can see how things work and play around a bit, but there will need to be some changes to the code based on what I've written above in order for it to work with your layout. If you need any help, please feel free to ask.

 

[tygrrboi]

Re: Pivot Exploding Donought Chart

TinyUpload.com - best file hosting solution, with no limits, totaly free


or

https://drive.google.com/open?id=0B5M48TEIr1guQVpJRTM2dkVfQ1k

 

[spycein]

Re: Pivot Exploding Donought Chart

Thank you so much tygrrboi!!! Works like a charm..

Now could you please create the send chart and link with this exploded part?

Thanks

 

[tygrrboi]

Re: Pivot Exploding Donought Chart

Send chart? What do you mean?

 

[spycein]

Re: Pivot Exploding Donought Chart

My apology.. it was typo..

could you please create the second chart ( sales person wise) and link with this exploded part ( Item wise chart)?