Amazing!
Agreed. This is a phenomenal report – thoughtfully and beautifully designed, with a great depth of analysis. The ring chart really jumps out as a visual element, and as always there is just a treasure chest of techniques to unpack here thanks to your incredible write-up. Also, the analysis page and particularly the issues for follow-up demonstrates so well how the visual analysis drives actionable insight.
A few questions:
- I know you decided not to use the ridge plot, but I would be really curious to see what the two category Python ridge plot you did generate looked like.
- Can you elaborate a little on how the pairs of NOW and USER functions were used to determine user interface, and how that affects your tooltips?
- I’m currently wrestling with versioning issues in a complex report I’ve launched recently that is regularly getting new features, and I love your use of that small multirow card to document your report. Can you talk a little bit about how you use the two elements in this card (i.e., what constitutes a .1 level version change, is the report ID constant regardless of version and what is that capturing?
Amazing job. 
- Brian
4 Likes
Very impressive Greg. What a reporting app!
2 Likes
Thanks again @EnterpriseDNA for hosting these challenges!
Here is my submission so far:
Edit: I was inspired by @Greg’s circle bars!
14 Likes
Hello EveryOne,
Here is my submission for challenge 8 which I had a lot of fun working on.
Data Model : -
For this challenge I created a Data model which consists of 6 Dimension tables and one Fact table in Power Query. ( I guess I am getting comfortable using Power Query to create a Data
model instead of SQL Server as I did before ).
Dimension Tables : -
The five dimensions were created using distinct values of :-
-
Projects
-
Issue Types
-
Statuses
-
Assignee
-
Watchers ( I Unpivoted Watcher_4 , Watcher_5 , Watcher_6 and Watcher_7 and merged them with Watcher and removed duplicates ).
and
-
Dates ( Thanks @Melissa ) .
Fact Table:-
- Jira_Tickets
DAX Calculations:-
Once the Model was created, most of the calculations were not too difficult. But, some of the data needed to be modified in order to make it work.
To verify the validity of the Tickets, based on @haroonali1000 's instruction, I used the following Measure:-
Verifying Tickets = IF (
ISBLANK (
MAXX ( Jira_Tickets, Jira_Tickets[Due Date] ) ),
"Incomplete Ticket",
IF (
MAXX ( Jira_Tickets,
DATEDIFF ( Jira_Tickets[Created], Jira_Tickets[Due Date], DAY )
) < 0,
"Implementation Issues",
"in " & MAXX ( Jira_Tickets,
DATEDIFF ( Jira_Tickets[Created], Jira_Tickets[Due Date], DAY )
) & " Days"
)
)
Then I created a Calculation Group in Tabular Editor to classify each ticket based on the above measure.
E.g. For incomplete tickets, the following DAX calculation was used in Tabular Editor.
CALCULATE (
SELECTEDMEASURE (),
FILTER (
Jira_Tickets,
[Verifying Tickets] = "Incomplete Ticket"
)
)
and the same for Tickets with Implementation Issues and the rest of the Tickets were assumed as Valid Tickets.
I also used Calculation Groups for to merge Statuses: -
E.g. the following Statuses were assumed as Resolved.
CALCULATE (
SELECTEDMEASURE (),
Jira_Tickets[Status] = "Completed"
|| Jira_Tickets[Status] = "Closed"
|| Jira_Tickets[Status] = "Done"
|| Jira_Tickets[Status] = "Resolved"
)
This is the first time I used Calculation Groups and I found them to be very useful ( especially for multiple Time Related DAX Calculations ).
Visualizations :-
For the theme of this Report I used one of Atlassian’s Logo and matching blue colors for most of the Report.
I took a visualization Idea of combining a Column chart and a Matrix to display bars for the months from one of the Top Experts on this forum (Thanks @MudassirAli ) .
Here is a link to the Report:-
P.S. The Decomposition Tree visual on the Summary page is not working on the link above but works on Power BI Services my Work space.
Thanks and Regards,
Nebiyu
10 Likes
@Greg
I love the simplicity in the report, headings and different colors used for different Page Headings of the report. You really are an expert now in using the Smart Narratives.
Ring chart looks really cool and awesome explanation as always.
Great Submission!!
2 Likes
@datazoe Wow for the design especially the navigation panel. What type of Red Color is this? Looks really amazing with Black background.
Great work!!
3 Likes
@MudassirAli This one is again using a template and specifically design / template from `https://www.numerro.io/ so I can’t take credit for the design, just how I applied it. I have been wanting to do a fly out navigation for a while. As I suspect, it’s a little annoying but not overly so :). Using these templates has been really helpful on my quest to make better designed reports overall.
3 Likes
@Neba I really like the look of your report! I am partial to the blue/white/grey a lot (which is why I forced myself to use black/red haha).
I am also liking this combo a lot too:
It’s very clear that Feb is higher than the rest and it’s then due to the POA&M Milestone Subtasks that’s driving it up.
5 Likes
Hey @datazoe , Thank you for your kind words. I saw your beautiful report too. Great choice of colors.
I am trying to break the curse too. I am inspired now. Note to self :- don’t be afraid of colors.
2 Likes
@Greg This is such a good looking report, and I love those ring charts (and was super sad they didn’t work on the publish to web version). I may have to try and create them in https://charticulator.com/. The way the data is presented is clear and clean, and I love the arrow through the report in the top left.
1 Like
Hi @Brian … thanks for your kind words. I’m honoured by the your comments and those of others. Thanks for your questions; here’s some more on your questions:
- Ridge Plot:
- here’s the Python sample I started from and massaged into Power BI; it shows vehicle mileage with one overlapping area chart per vehicle class
(My plan is to keep working on this on my own time, as I’ve invested a lot in trying to understand and implement it, and think and hope I’m close to being able to show [from the C8 dataset] date on the x-axis, total issues on the y-axis, and project on the “z”-axis (i.e., one “ridge” per project)
- Environment and Report Admin:
I used the following two measures to give me a TRUE if the report was being accessed in the stated environment:
Is Desktop =
IF( NOW() <> UTCNOW(), TRUE(), FALSE() )
Is Service =
IF( USERNAME() = USERPRINCIPALNAME(), TRUE(), FALSE() )
(My understanding is that the best practice is to always use the USERNAME() to USERPRINCIPALNAME() check to determine environment, but this check doesn’t seem to work properly on my local, hence the “hack” with checking a difference between the local time and UTC time [I know this falls down if you’re located in the time zone where local time = UTC, but was a quick-and-dirty way forward].)
I can then branch from these measures into my tooltip text, like:
Tooltip Current =
SWITCH( TRUE(),
[Is Desktop] = TRUE(), "CRTL+click to go to the CURRENT page...", -- tooltip for Power BI Desktop
[Is Service] = TRUE(), "Click to go to the CURRENT page...", -- tooltip for Power BI Service
BLANK()
)
and then use the measure as the “field value” in the tooltip
- with respect to report ID and versioning, it is implemented as per the customer’s standard, but if they have none (or don’t use it rigorously), I usually do the following:
- set a [Report ID] measure hard-coded to the report ID; this does not change in the lifecycle of the report
Report ID = "SD007"
- set a [Version] measure hard-coded to the current version, and increment the number each time I publish the report using a major.minor paradigm, where the major number is incremented for each major release (e.g., initial, colour theme and/or major layout changes, new data source, new calculations, new visuals, etc.) and the minor number is incremented for each minor release (e.g., new visual on existing dataset, bug fixes, etc.)
I made up a version for my C8 report as
Version = "V1.2"
These two measures were then used in a narrow multi-row card and placed in the bottom-left of all report pages. (I used them on the report Info Tooltip page as well.)
3 Likes
Thanks so much for your kind words @datazoe. I’m a bit puzzled by the ring chart not displaying properly for you … all my screenshots were taken from the service and the ring chart works there for me in the service, there’s just a lag of a number of seconds (about 6 seconds for me) while Power BI exports the data and redraws the Python visual (not ideal, and I’m sure there’s an admin setting to improve this, but I’m still in the infancy of my learning).
Perhaps Python needs to be installed on the workstation viewing the report? (Although this seems unlikely as Microsoft has usually handled something like this.)
Thanks again,
Greg
4 Likes
So wonderful and also all the text to the model and submission. Thanks.
2 Likes
Dear all
Here comes my submission.
Link PBIX fil: Challenge 8 - Data From Jira IT service Desk Analysis.pbix (367.1 KB)
The Challenge was delivered in one Excel sheet.
Started to orientate me in all the columns and what it means. BrianJ shared a link from Jira, what the difference columns means. https://innovalog.atlassian.net/wiki/spaces/JMWEC/pages/108200050/Standard+JIRA+fields
The task was given in the Brief.
I had two goals.
First goal - page one.
Give an overview of the tickets and shows the status grouped from closed ticket and ticket in a progress. Show on which type of task the tickets regards. Tickets which isn’t closed yet and on whom supported the open tickets are allocated to.
Secund goal – page two.
How are the tickets type divided from high to minor priority both in number of tickets and in percentage.
Then also to show how much time is used on each tickets. It doesn’t tell how much time there has been used effective on the tickets but tell how much time has gone from case opened to the case is solved/closed.
The purpose for this page - Analyze – so the management can see where the support team use their time and on which type of cases. In that way the management have information to dig deeper and see what there can be done to reduced the number og tickets.
Measures:
-
Number of tickets
-
- Pct tickets of total of tickets
Divide the type of #tickets in the two project names Help Desk and POAM tracking:
Pct Ticket(2) =
VAR _AllCountTicketIssueType =
CALCULATE (
[No Ticket];
REMOVEFILTERS ( Jira[Issue Type] ) //Remove the filter on “Issue Type” to get 100% of the subtotal “Project Name”
)
RETURN
DIVIDE (
[No Ticket];
_AllCountTicketIssueType;
0
)
-
Average working days, from the ticket was opned and it solved and closed.
-
#tickets solved after Duedate. Not all tickets have been opened with a duedate
-
Average minutes has gone from opened the tickets and closed.
Powe Query:
Only done some minor change. Column name and removed the “@” on the supporter name.
8 Likes
Hello everyone!
For my submission I added an additional “Description” field which is common in Jira tickets, but primarily limited the use of this field to the topic modelling report. There’s several machine learning algorithms in this report:
- Topic Modelling (Python)
- K-Means Clustering (Python)
- Anomaly Detection (Python)
- Auto ML Forecasting (R)
- Auto ARIMA
- Auto ARIMA with XGBoost Errors
- ETS
- Prophet
- LM
- XGBoost
- XGBoost Workflow
- Multivariate Adaptive Regression Splines (MARS)
If R or Python visuals had the same interactivity/reactivity and all the features that they do using Shiny or just in standard R visuals then I’d have had these done live, but so that I could fully take advantage of Power BI’s visuals and suite of features I had Power Query run the scripts.
This is just the second draft of the dash and I’d want to go through another round of development before presenting it to a client/project lead, but I think it came out pretty good. I’d want to at least add some navigation features, tune the ML algorithms, and optimize the performance, but I’d need to understand more from the client/project lead before I could do that.
14 Likes
@Neba - love the report, the layout and the color scheme. The switchboard has a nice user interface. Great job!
-Tim
1 Like
Hello all,
Challenge number 8 was very interesting . I never worked with JIRA data before but I think it is an awesome use case.
The most challenging part for me was to find a way to communicate a lot of information/analysis in an interactive, simple and comprehensive way (and in the right order). I explored and tested different ways of presenting this data in the last 2 weeks.
In the end, I decided to use some simple visualizations and bring value with the use of interactions. Doing so, I tried to avoid pushing too much information to the user and lose his/her concentration. I m trying to let the user discover the data, the messages, at their own pace. For this I am trying to build a framework where there is a lot of flexibility for user analysis.
In my quest to simplification, I used for this report only 1 page and used bookmarks
Please find below my submission for challenge number 8.
Toggle Activated below left
Toggle Activated below right
Page 2 - Timings
Page 3 - Data Quality and Recommandation
Zoom on Status
Below is a quick gif of the report
13 Likes
Wow, this is amazing! Looks fantastic, and a lot of techniques I’ve never seen used in Power BI before. Can’'t wait to see your full writeup to better understand how you did some of this.
BTW - spot on re: the Description field. I anonymized this data and there was simply too much PII in that field, so I had to remove it.
Phenomenal stuff.
- Brian
2 Likes
@bradsmith Wow so many cool visuals you used there! I love the layout too! How did you do the Average Age like that?