Forum Discussion
Request Help Optimizing Donut Chart
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!
I hope this will do it
https://drive.google.com/file/d/1Yt2BYEM8mrM5IqVrC7PAFIN4TI64-Wk5/view?usp=sharing
30 Replies
Hi CoreyP,
There are a couple of steps that you need to follow:
1. Using a Union, merge another blank table and name the activity "Other", hours as null
2. Create a measure for total hours Total Hours = sum(Table[hours])
2. Create a Rank measure Rank = rankx(all(Table[Activity Type]), [Total Hours], , DESC)
3. Then create another measure
Hours = IF (Table[Rank] <=1, [Total Hours],
IF(HASONEVALUE(Table[Activity Type]), IF(VALUES(Table[Activity Type]) = "Other",
SUMX(FILTER(ALL(Table[Activity Type]), [Rank] > 1 ), [Total Hours])
)
)
)There are a bunch of steps, I can understand if it gets confusing. Hope it helps.
I'm not quite sure I follow.
I've got my labor fact table which looks like this:
Date | Employee | Activity | Labor Hours |
Then I've got a dimension table for the activities:
Activity Number | Activity |
1 Data Entry
2 Inspections
3 Loose Pick
4 Putaway
etc
Check whether the below blog is useful.
Comparing the same visual side by side in Power BI by controlling filtering bahvior
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.
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.
This looks like what I'm looking for! Let me give it a go real quick!
Thank you! Stay tuned..
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?
Did you tried the measures I posted here, I assume that will do it.
