Power BI Challenge 8 - Jira IT Service Desk Analysis Entry from Mudassir

[EnterpriseDNA]

Here’s Mudassir’s entry for Power BI Challenge 8. @MudassirAli, feel free to add other details of your work.

Here is the link to the report:

Power BI Report

And here’s how Mudassir described it:

Welcome to the World Of Charticulators
Here is my submission for Challenge # 8

Finally managed to complete the report. Some visuals took days to complete and it was really a Rollercoaster ride.

Using The Report
Tickets Created
I tried to build the flow of the chart by first analyzing what Peak Periods are. I tried to show the Peak Periods by Time of Day also so management has the idea as to what time of the day are the busiest for Technicians. You can see that Borders of the Calendar looking visual are Red for some but Grey for others. I made an assumption that the # of issues more than 100 in a month will be considered as a busy period. Therefore, the Borders will turn Red Dynamically whenever the # of issues are more.

Moreover, If you click on the top right corner, heatmap looking table will pop up giving the summary of Peak Times & Days.

Tickets Composition
Here I have used the Dot Plot Visual to show the # of Tickets Created, segregated by Priority and highlighted by the Status of the Tickets.

Tickets Resolved Time
The # of days taken to resolve the issues are highlighted here divided into years. The visual itself conveys a lot of information but I have also marked some items for management to analyze.

There were some items whose Created Dates were higher than Due Dates so here I assumed that Created Date column was valid as we already are facing issues with recording of Due Dates in the system

Tickets Status
Here I have converted the table into Bar Chart looking Table that can easily highlight the issues. I have used OPEN DAYS ** term that actually means Active Tickets & Active Tickets Days.

If you click on the top right side, analyzing the Status Trend over the Years will be much more easier with the use of Dumbbell & Lollipop Charts

Workflow
At the end the system issues and allocation of resources are highlighted.
Here I have used Waffle Charts instead of Card visual as Waffle charts are perfect for comparison between 2 categories.

Visuals Used
Native Bar Charts
Native Column Charts
Dot Plot by MAQ Software
Enlighten Waffle Chart by MAQ Software
Dumbbell Charts designed in Charticulator
Bar Charts designed in Charticulator
Lollipop Charts designed in Charticulator
HeatMap designed in Charticulator
Slope Charts designed in Charticulator

I know I haven’t explained much as I am exhausted and have to get prepared for the grinding starting tomorrow. I haven’t worked so hard before in completing the project but now I feel satisfied as we can have Tableau looking visuals in Power Bi too

This past 2 weeks have been a great learning experience, diving into R scripts, Statistics and Charticulators.
Thanks @BrianJ & @AntrikshSharma for your help along the way.

Thanks @haroonali1000 for coming up with the right dataset at the right time

P.S. Charticulator is not easy when you are trying to design visuals and to make it work with the report design but custom visuals communicate way too much information than native PBI visuals.

To learn about the real-life scenario presented for the challenge, be sure to click on the image below.

[MudassirAli]

This Challenge taught me a lot of things including Ranks with Tie Breakers (Thanks to @AntrikshSharma & @BrianJ), adjusting Ranks to work with Charticulators, lots and lots of Conditional Formatting and last but not least designing own custom visuals. Frankly speaking, I love the liberty of incorporating the creativity in designing the visual and conveying the story with visual itself which I find very hard with native Power Bi visuals.
I wouldn’t have dived into the Charticulators if it wasn’t for @datazoe (from whom I first heard about this cool visual designer) and EDNA forum (where I stumbled upon a post inquiring about the Tableau looking line chart). Solving the question on the forum gave me the idea what this designer is capable of.

First I decided that I will just have custom designed HeatMap and Slope Chart but soon things became crazier. The biggest problem wasn’t designing the visuals but rather What I wanted to design, What story I wanted to convey & How I wanted to convey it.

Data Transformation In Power Query

First what I did was to extract the names form the emails. To do this, I selected all the columns with emails and selected Extract from Transform Menu and under extract, chose the option Text Before Delimiter and chose @ as the delimiter.

Secondly, I tried to extract the Time for my HeatMap from the Created Date column. I duplicated the column and selected Split Column and selected Space as the Delimiter

Now the Created Time looks like as follows:

Now separated the Hour form Minutes by again selecting Split Column and choosing Colon as the delimiter

Then extracted the AM/PM from minutes by selecting Extract and under extract chose Text After Delimiter and selected Space as the delimiter

The last thing required to do was to merge Hour with AM/PM. I selected the columns I wanted to merge and selected Merge Columns

Lastly, to get the # of watchers I duplicated the Data Set Query and kept only Issue Key & Watchers Columns then Unpivoted Watchers Columns and Filtered Rows from Value Column

After that Removed the Attribute column and added Index

Now Grouped Rows so I have the Total # of Watchers against the Issue Keys

The last thing was to Merge the # of Watchers back to the Original query. I selected Merge Queries option and Merged it by Issue Key, Expanded the Columns & Replaced Null with 0

Now all set to move to Power BI.

Main Calculated Columns

1. Days To resolved
   Days To Resolve =
   IF(‘Jira Data’[Resolved]=BLANK(),
   BLANK(),
   DATEDIFF(‘Jira Data’[Created Date],‘Jira Data’[Resolved],DAY))

2. Status Modified = (Combined Status into 3 categories)
   IF(
   AND(
   ‘Jira Data’[Resolved] <> BLANK(),
   ‘Jira Data’[Due Date] <> BLANK()),
   “Resolved With Due Dates”,
   IF(
   AND(
   ‘Jira Data’[Resolved] <> BLANK(),
   ‘Jira Data’[Due Date] = BLANK()),
   “Resolved Without Due Dates”,
   IF(
   ‘Jira Data’[Resolved] = BLANK(),
   “Open”,
   BLANK())))

Peak Period
I used the Calendar looking style and HeatMap to show the Peak Periods by conditionally formatting the Highest Tickets Created with Red Color and by using the following DAX formula:

VAR
TicketsCount =
CALCULATETABLE(ADDCOLUMNS(
SUMMARIZE(‘Date’,‘Date’[DayOfWeekName]),
“Tickets”,
[Count of Tickets]),
ALLSELECTED())
Var
MaxValue=
MAXX(TicketsCount,[Tickets])
Var
Currentvalue = [Count of Tickets]
Return
IF(Currentvalue=MaxValue,1,0)

The Borders of the Calendars will change Dynamically to Red if the # of Tickets Created are more than 100 in a month to easily identify the busy periods. I did this by using the Blank Button as Borders and placing the measure to conditionally format it. Measures were created for every individual month as follows and then were placed in the Outline section of the Blank Button

With this, it was easy to identify that the First & Last quarters were generally the busiest periods of the year.

Tickets Composition
The Dot Plot custom visual by MAQ software was used here to show 3 dimensions:

1. Tickets Created shown by Size of the Bubbles and broader the column, higher the tickets created in that Issue Type.
2. Showing the priority in the X-Axis
3. Showing the Status of the Tickets by different colors (Grey = Tickets Resolved Without Due Dates, Yellow = Tickets Resolved having Due Dates, Red = Open/Active Tickets)
   
   
   
   
   Turn On the Jitter Effect option from the Format Pane
   
   

Tickets Resolved Time
Here where the Own built custom visuals were used. Power BI do have custom made Slope Charts & Dummbbell Charts but they aren’t as flexible and painful to work with. So much information can be obtained easily for e.g. I selected the Year 2020, looked at the left hand side visual and noticed one Issue Type was taking more days to resolve. When I clicked on that Issue Type it showed that in 2020, only one person was dealing with that Issue and that’s causing longer resolve times

Moreover, by differentiating with colors it can be easily noticed that some Technicians weren’t assigned Tasks

Tickets Status
Tickets status was the hardest part in terms of presentation. The chart containing the Status information by Assignee was first designed in one visual only with multiple Y-Axis however, when I filtered the data I noticed that Bars with low values weren’t showing any Bars and it wasn’t looking good visually. Then I designed different charts for every column and then struck with a problem. All the assignees didn’t have Open Tickets so the Y-Axis position was misaligned in the Open Tickets Column. The details of how I fixed it will be explained when I am finished completing the write up on how I designed the visuals and what problems I faced in designing them.
After that hit the roadblock when I tried to design Bar Charts by Issue Types shown in the Right Hand section. The reason of not using the native Bar Chart was that it took a lot of space in Y-axis when Issue Types were shown and didn’t look good at all

Here the problem was to force the Charticulator to show the Bar Chart sorted by Descending order which was very painful to do as it only sorts by Categories not by numerical values (Yes it also does by numerical values but there are other problems come with it). Here @AntrikshSharma & @BrianJ helped me in achieving the desired visual.

I also showed the Ticket status in Dumbbell Chart style categorized by Years and the color was assigned to every Year. By then I knew how to create a Dumbbell Chart so there was no issue here however, creating Lollipop charts shown at the right hand side were the hardest. The hard part was to put the Text on top of every category. Spoiler Alert: These are actually 2 charts combined in Charticulator, one showing the values and bars & the other only Text. This was something I didn’t see anywhere else and this visual is my favorite one.

Workflow
Here I used the Enlighten Waffle Chart by MAQ Software as these charts are very good in showing comparison between 2 categories only. So I showed different analysis containing Project Leads & Project Name and the Texts are all dynamic created via measures.

To analyze the workflow I divided this section into 3 categories (What happens to Resolve Days in relation to # Watchers? Is the workload evenly distributed? What’s the problems noticed in the system?

The last section is further broken down into two categories (Open Tickets with No due Dates & Incorrect status in system) and placed the Next & Previous button to navigate. The texts are all dynamic in this page

Moreover, also placed the info icon for the management to generate the list of issues.

What I couldn’t do?

1. Didn’t place Report Page tooltips that could have added more information like % of same day resolution this year vs last year. I created the measures but ran out of time.
2. Wanted to show weekly trend of Resolved Tickets by Assignee & by Issue Types. Wanted to use the Bar Chart and in front of the Bar Chart wanted to show the trend by each assignee & issue type as follows:
   
   

Navigation Bar
I used the navigation Bar inspired by the Menu Bar of excel or outlook and also made use of the Blank Buttons option to change the colors of the Page icon when in default Mode and when you hover. This will give the end user the idea at what page they are currently in. Used Green for the current page you are in & Red when you hover over it

Why I used the Charticulator
To be frank, I never liked the native visuals and these visuals haven’t been changed much since Power BI launched. Secondly, nobody has actually dived into it and used the custom made visuals in actual reports. If you are trying to design a custom visual just for fun then it wouldn’t take long to do so. However, if you are actually trying to incorporate the visuals in the actual report, aligning them in a page, making them interact with other visuals and above all to make them aesthetic looking are the challenges you will face.

Documentation
I am working on documenting how I made the visuals. I think making a video will be much easier.

Conclusion
I had a lot of fun completing the challenge and I will keep exploring and keep learning.

Thanks to EDNA experts and team members for making it happen and keep motivating us to do something better each time.

That’s all from my side for this challenge.

[AntrikshSharma]

Great stuff!!! I can’t start to think how much efforts you have put in on such a great report, I create a bar chart with default color and I look at it and think no less of myself than picasso!! I am telling you, you publish this on PBI Community and you WILL WIN “5’VE GOT THE POWER” T-shirt, There is a reward every month I think. @quantumudit might know more as he won one.

Regarding the date time columns you know you can duplicate the columns and change the data types too! to just date and time, I think you might find that a pretty neat way, and I think it would be efficient as well but that’s just my hunch.

[MudassirAli]

@AntrikshSharma Thanks for your feedback. Your Ranking with Ties was no less than a magical stuff. I don’t know why but I can’t login to Power BI community. I tried many times it shows that I am logged in but whenever I try to comment it says, LOGIN first . Maybe those folks don’t like me.
I tried the Date/Time but it wasn’t giving me the desired result so I went the long route. I first changed it to Time and then extracted Hour & AM/PM. There will be more efficient way for sure and our Agent M @Melissa might tell us about it .

[AntrikshSharma]

That used to happen with me as well, I think you have to wait 20-30 seconds for them to log you out. If you have an Azure Active Directory account you can create a new account to try and login with that ID. Or maybe try with incognito mode.

[MudassirAli]

@AntrikshSharma hahahah Incognito Mode. I will try that

[Melissa]

Hi @MudassirAli,

Amazing report and write up, love it, absolutely fabulous!
Can’t wait to see your video’s

As for extracting hours from a datetime value, I’d probably do something it like this:

Table.AddColumn(EnterYourPreviousStepNameHere, "Created Hour", each 
    each Text.Upper( Time.ToText( Time.From( [Created] ), "h tt" )), 
    type text
)

[quantumudit]

Hey @MudassirAli,

I’m literally speechless !! You’ve created a masterpiece and I really liked the way you utilized charticulator in your design.

As @AntrikshSharma suggested, you must post this amazing report in Power BI Community (You need a professional account) and I’m sure that you’ll get amazing gifts too.

I would really like to see more such amazing reports from you. Its truly inspiring.

[haroonali1000]

Wow @MudassirAli!

A great write up, will have to read that a few times to get my head round some concepts.

Thanks for sharing.

Haroon

[sam.mckay]

Wow Muddassir.

Once again a world class report. This is super impressive. From the actual analysis being crazy good to the report design and navigation also taking this to another level.

I don’t know how much time you spend on these but I cannot express how much value your giving to the community by creating reports such as this and detailing how you do it. You’re really enabling the community to see and leverage off some of the best Power BI work out there I believe.

Some of the innovative designs you have created in your tooltips and with your filtering area. I find myself being inspired once again by what you have completed. I can’t wait to find the time to work on the next challenge to utilize some of your ideas.

I want to make sure anyone reading this takes a good hard look at what you have been able to create. And really steal some of the design aspects and ways that you have laid out each page.

The color scheme works well and also really like how you’re brought together various different visualization techniques to create new and interesting insights that historically were very difficult to achieve.

As mentioned earlier I would really like to start creating some content with you around how you work through your report designs and development. I think there’s a lot of creative thinking that others would benefit from around how you approach your reports.

Something we can look to do in the near future hopefully.

Sam

[MudassirAli]

@sam.mckay
First of all thank you for your detailed feedback. The drive, the ideas and the motivation all came from the EDNA experts & members. These challenges are just excellent tools to learn advanced PBI skills in a short period of time.

I would be glad to create a content, help the EDNA community and collaborate with the team to take PBI development to next heights.