Request Help Optimizing Donut Chart

[CoreyP]

Hello Power BI guru's,

Thanks for your continued sharing of expertise and helping out us little guys to become the proficient business intelligence experts we so desire to be!

PROBLEM: I have a bunch of labor data composed of different "activities" or tasks. I want to display a donut chart that reflects the percentage of that activity's labor to total labor for all activities. On this same report page, I have a labor hours over time bar chart, to see trends in total labor for each activity, SO, I have an activity slicer, so that you can select whichever activity and view the trends. 

WANT: I want the donut chart to change, in addition to the trend bar chart, whenever I change the activity slicer, so that the donut chart shows ONLY the selected activity vs all other labor hours. Here is a picture of what I want vs what I have.

The only way I was able to create the one on the left, was to select the "Data Entry" activity, right click and say "Create Group." Then, it's Data Entry vs all Other. However, I would have to create a seperate column for EVERY activity so each one would be in it's own group, but then the slicer wouldn't work because it's just for activitiy general. 

Can anyone help me out? I'm sure it's something simple. Please don't tell me to just not use a donut chart. lol.

Thanks again!

[anandav]

@CoreyP,

Ok. I could suggest a solution close to what you want.

From your data set I added a new numeric column. Then I created a new summary table as below.

The visualisation from the new summary table is below.

As you can see the bar chart shows details in a meaningful way. The donut chart is not that useful when no filters is selected.

When you select an activity from the slicer, the donut chart is 'some what' useful but I cannot think of a way to display the selected activity from the slicer in donut chart label.

You can display a text filed with the selected activity an display at the top of the donut chart. Google for "power bi dynamic text on selection" and there is plenty of help ont his from experts.

May be this gives you some idea though it does not totally fulfill your requirements.

Hope it helps.

[CoreyP]

anandav,

That definitely gives me some food for thought! Thanks for working so hard and taking the time to provide such a detailed walkthrough with screenshots, that really helped me conceptualize what you were doing.

I want to post another example of what I'm trying to achieve in case I didn't explain it sufficiently before. So, my report is like this:

The bar chart shows the sum of labor hours, filtered by activity through the slicer, by week ending periods. The pie chart shows the sum of labor hours for the selected activity, versus, the sum of labor hours for all other activities. (A bonus would be if you clicked on a bar in the chart, it would filter the totals in the donut chart for just that week ending period)

Then when a user selects another activity from the slicer, the donut will show, again, the selected activity vs. all other. Like so:

It's almost like a "dynamic grouping" or something, I dunno. 

What do y'all think?

[anandav]

@CoreyP,

Not sure whether I am missing somthing but what you explained is what I have suggested - only the donut chart and slicer (igoniring other visuals for simplicity).

Selected acitivity hours vs. all other activity displayed as a % in the donut chart.

[parry2k]

It can Be done for sure , can you share sample data for both tables in an excel sheet and I will send you the solution

[CoreyP]

Great! Here's some screenshots and an example doc. 

Activity Dim TableLabor Fact Tableerr, I don't know how to attach an excel doc... 

[parry2k]

Did you tried the measures I posted here, I assume that will do it.

[CoreyP]

I tried what you said, but it doesn't work and I'm thinking I misunderstood what you said because what I'm doing doesn't seem logical. 

Activity table:

Table Name: Activity

Column 1: Activity Number

Column 2: Activity

Labor table:

Table Name: Labor-ALL Activities

Column 1: Date

Column 2: Lastname, Firstname

Column 3: Activity

Column 4: Labor Hours

Column 5: Labor Cost

The measures I wrote:

Selected Activity = SUM(Activity[Activity])                   (Which doesn't make sense to me, because you can't sum text)

Other Activities = CALCULATE(SUM(Activity[Activity]), ALL(Activity)) - [Selected Activity]

I probably did that all wrong, huh?

[parry2k]

no you are right, what you trying to show in donut chart?

Total Activities or Total Hours?

 
Didn't read your thread in detail but I just posted the solution but what you are trying to do is not something which cannot be done.

[CoreyP]

Total hours. 

So, 

Total Hours = SUM(Labor-ALL Activities[Labor Hours])

Then, when the slicer filters per activity, it would become:

Total Hours of "Selected Activity" = CALCULATE([Total Hours], Activity[Activity] = "Selected Activity") 

...where "Selected Activity" is Data Entry, or Dimming, or whatever.

I want my donut chart to show: 

Total Hours of Selected Activity vs. Total Hours 

For instance, if the total hours equals 73,660, and Data Entry is 22,740, the donut chart would show Data Entry as 31% of Total Labor. 

Does this make sense, or are you saying that this cannot be done?

[parry2k]

Add two measures, one for selected value and another for others

Selected Activities = SUM(Table[Activities]

Other Activities = CALCULATE(SUM(Table[Activities]), ALL(Table)) - [Selected Activities]

Drop donut chart and these two measures in the values.

[anandav]

@parry2k,

Your mesaures are a very simple way to do what I have suggested (replacing teh activity with hours). Very efficient solution (for me).

[CoreyP]

It's almost like I need to dynamically define what the "Other Total" should be...?

[parry2k]

@CoreyP as mentioned earlier, can you share your data in excel using google drive or onedrive what ever works for you

[CoreyP]

Click Here for *.xlsx of my tables

Note: I removed the employee name field, as well as the hourly rate and labor cost fields because that information in confidential, and not needed in this example, i don't htink. 

[parry2k]

Sounds good. stay tuned.

[CoreyP]

Thank you again so much for all your help.

[parry2k · Accepted solution]

I hope this will do it

https://drive.google.com/file/d/1Yt2BYEM8mrM5IqVrC7PAFIN4TI64-Wk5/view?usp=sharing

[CoreyP]

It works SO well. And such a simple, elegant solution. I knew it would be something easy like that. 

[parry2k]

Good to hear that it met your requirement. Have fun!!!

[CoreyP]

Clearly, I'm a novice with Power BI... I discovered PBI about 2 months ago and have been cramming and learning as much as possible. 

Fun? YOU BET! This is the coolest thing I've ever done. It incorporates some of my favorite things/top skills such as math & forumlas, and coding, with graphic design/ability to be creative, and story telling into one amazingly powerful business intelligence program. 

I've actually decided this will be my niche, and I plan on making a career as a Business Intelligence Analyst. 🙂

...You haven't seen the last of me yet.