Forum Discussion
Displaying multiple measures in bar chart
Hi there,
I have data about the time taken to complete activities. I have created a measure that calculates whether the job has done before the estimated time or after the estimated time. Measure produces the output in minutes. If minutes are negative then it means the job took more time to complete than the estimated time (let's call overtime) and if minutes are positive then it means the job took less time to complete than the estimated time (let's call undertime).
I want to show the distribution of overtime and undertime tasks in the form of bands as shown below:
The x-axis of the picture shows the minutes task taken to complete. for example, -15 means a band that shows the count of activities that took up to 15 more minutes (overtime) to complete the task. I have created measures that count how many tasks come into each band. For example, the [Overtime 15 mins] measure calculates the number of tasks that took up to 15 minutes more than the estimated time. Now I got 13 measures that calculate the number of tasks completed for each tick shown on the x-axis.
The problem is that I don't know how can I display the information as shown above. I don't have any table (or any other thing) which has the values displayed on the x-axis of the picture. Could anyone advise how could I display multiple measures in the form of bar graph as shown above?
Sample file can be downloaded from here . All related measures are present in the table named 'measure'.
Dunner2020 Please see attached file (below signature). You need to create the calculation as COLUMN, not Measures. Then right click the new column and choose "New Group" (select edit group to see the grouping I chose).
11 Replies
AllisonKennedy , Thanks for the solution. The only problem with the binning/grouping is that: Bin 0 also counts the Minutes Diff that has non zero values. Is it possible that we define bin in such a way that bin 0 only counts zero values??
Dunner2020 You can try to cheat the histogram distribution, but this moves the values to the 15 category so that 15 category includes values greater than 0 and less than 30. That makes this category twice as big as all the others, so not really a fair distribution, but I can see why you would want to do this.
Another option is to highlight on the distribution which values are exactly equal to 0. See what you think of the file below signature. Colours need updated, and you probably want to filter out the blanks, but the concept is there.
AllisonKennedy Thanks a lot. You are saviour. The second graph is what I am after. I didn't know that we could use the switch statement to allocate the bin. I think which I still could not understand is that you mentioned about bin 0,15 and -15 in switch statement and mentioned their range and left remaining to allocate according to minutes diff. For instance if minute difference is 78 then it allocates to the 78 bin. When we plot the information, it is still showing the bin with 15 min difference and counting accordingly. Is it happening because we made a group or is it because of histogram? Sorry if my question sounds stupid.
Dunner2020 Glad it's working - you are making sense, now let's see if I can make sense with my reply: I used the SWITCH just to do the minimum bin (ie making 0 only 0 and allocating the values around 0 to the other value), then I used the current value of Minute Diff for all other values, and used the New Group to create the same bins as in my first solution, but because we have replaced all Minute Diff between 0 - 15 min with a value of 15, this will move them to the 15 bin.
Dunner2020 I would suggest that you have 1 column that does this binning/grouping for you. There is a histogram visual that will do this grouping for you automatically: https://spreadsheeto.com/power-bi-histogram/
Or you can Right click on the Time column in the Report view and select New Group, then group by bin size, 15. For this to work, you'll need the Time to be a COLUMN (not a measure as you currently have it).
Can you share a preview of what your table/data model looks like currently if you need more advice?
Dunner2020 Please see attached file (below signature). You need to create the calculation as COLUMN, not Measures. Then right click the new column and choose "New Group" (select edit group to see the grouping I chose).
Dunner2020 , seem like you need dynamic segmentation
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
https://www.youtube.com/watch?v=CuczXPj0N-k
refer: https://www.daxpatterns.com/dynamic-segmentation/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualizationAllisonKennedy amitchandak , Thanks for your reply. I have added a sample file link in the actual post.
Hi,
I'd like to help but your PBI file has a lot of tables which is causing confusion. Please share minimal tables with oly information required for solving your current question.
Ashish_Mathur , I have removed the extra tables and updated the post with new link.
In the Outages and Interruption Table, create a column of difference and share the download link of that updated PBI file.
