CODEWITHSUNDEEP
powerbidrilldownpowerbi-desktop
user2092493
user2092493

Reputation: 19

Create Drill down on Stacked Chart in Power BI

I have some sample data as shown:

     Site   Ready   Not Ready   Training    Break    Other
      A     78%        22%          0%        5%      16%
      B      57%       43%          0%        6%      37%
      C      56%       44%          0%        1%      43%
      D     94%        6%           0%        0%      6%

Where 'Site': Call Center Site, Ready: % of time agents are in a ready state

Not Ready: % of time agents are in a not ready state (further broken down by 'Training', 'Break' and 'Other' states)

I want to show a stacked graph that shows a proportion of time in Ready vs Not Ready State; and when drilled down on Not Ready, it should show proportions of Training, Break and Other states. The Excel equivalent of this would be a pie of a pie chart, but I am stuck when I try to do this in PowerBI. Any advice would be helpful. Thanks!

Upvotes: 0

Views: 1636

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

First, you'll want to reshape your data to the following:

Site  State      Substate  Value
--------------------------------
A     Ready      Ready     0.78
A     Not Ready  Training  0.00
A     Not Ready  Break     0.05
A     Not Ready  Other     0.16 
B     Ready      Ready     0.57
B     Not Ready  Training  0.00
B     Not Ready  Break     0.06
B     Not Ready  Other     0.37 
C     Ready      Ready     0.56
C     Not Ready  Training  0.00
C     Not Ready  Break     0.01
C     Not Ready  Other     0.43 
D     Ready      Ready     0.94
D     Not Ready  Training  0.00
D     Not Ready  Break     0.00
D     Not Ready  Other     0.06

To do this, delete the Not Ready column and unpivot the remaining columns except Site to create the Substate column. Then create the State column as a custom column:

if [State] = "Ready" then "Ready" else "Not Ready"

(Note: You could just as easily create this with a DAX calculated column instead.)

Once your data is reshaped, it's much easier to work with. Just put State and Substate in the Legend box of a pie chart and the Value in the Values box.

Top Level

From there you can either drill down on Not Ready or pass to the Substate level of the hierarchy:

Drill Down Next Level

If you're interested, there is also a Drill-down donut chart that is pretty nifty for this sort of thing.

Upvotes: 1

Related Questions