Trying to convert the data from a pivot in Excel to a visual in PBI

[AndyTilley]

Hi

So, I am stuck.

I have been given an excel sheet(likely a challenge) to get into a visual.

I am still a beginner at PBI and this one has me stumped.

The data in excel has been put into a pivot table, and then transferred to a table and then a chart, to show backlog of work outstanding and completion rates.

As you cant do a calculation in a pivot table the reference table is used to data the chart.

So my boss has data for two pivot tables, one for the opening date of the ticket and one for the closing date.

hes then doing a comparison on the pivots to the reference table deducting the amount of open /closed in one week to give the backlog and status.

What I am trying to achive is to get a running chart showing tickets opened in week X , the number of tickets closed in week X but the way hes aligned the data its crazy and I can't get any visual to display what I want. I can display the amount of tickets open , now, but I really need a chart to see if we are progressing or failing as a team so I can identify where we should put our resources into.

Im guessing I need a measurement of some sort (which I am still getting my head round from Excel as its is similar, but different) and have tried so many things and wasted an entire day with zero results.

I have linked the files in a dropbox link which can be played around with as much as needed. Its driving me nuts, and being in lockdown as well I am going insane fast ! 

😁

Drop box folder 

[MFelix]

Hi @AndyTilley ,

To what I can understand you want to calculate the status of the tickects at a given date(s) so you want to have all the tickets that are open on X week and all tickets that were closed on X week correct?

For this you need to create a calendar table check the links below to have a way to calculate your table:

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

https://exceleratorbi.com.au/build-reusable-calendar-table-power-query/

Now you just need to create your measures, but I have some questions:

• What is the columns used for Start and close dates?
  • You are using the logged date on your visualizations but you also have Start Date
  • For Closed date the column is empty
• The excel file only have one table no pivots or charts is this correct?

I create the following measure:

Open Tickets =
CALCULATE (
    COUNT ( 'Full ticket data'[Id] );
    FILTER (
        ALL ( 'Full ticket data'[Logged Date]; 'Full ticket data'[Completed Date] );
        'Full ticket data'[Logged Date] <= MAX ( 'Calendar'[Date] )
    )
)

This give a cumulative count of the open tickects.

We need to add on this the end date column in order to get the open tickets on that date but is just an example of what you need to calculate.

Can you share some more infomration about the end result and how the information that is on the pivot tables and charts.

[AndyTilley]

achh my mistake I forgot to upload the Pivot table spreadsheet.

If you check the folder now there is a  quoted repairs and backlog spreadsheet/ Quouted repairs APR25 2020 tab, it has the pivots, the reference table and the final charts, which is what I am trying to produce from the data sets, without the need to use pivot tables.

I believe you cannot do functions like subtraction on a pivot table , so my boss has created two pivot tables. each count the amount of unique work reference numbers (ticket numbers) opened and closed in the numbered weeks 1, 2, 3 etc.

week 2 cell C9 shows 3 calls opened in top pivot

week 2 Cell C25 3 calls closed second pivot

3rd table carries out subtractive functions

4th and final table carries out subtractive functions for week 3 to week 2 showing the run rate we are clearing the work down I guess.

The charts at the bottom are referencing the bottom table

I am trying to reproduce those charts with the data I already have that my boss used in the spreadsheet. and a few visualisations more already on the PBIX file such as a timeline(showing when we started and stopped the job indicating how long it took to carry out repairs and if we are missing KPI)

unfortunately my boss is money conscious even though I tell him the desktop PBI is free to do all this only the pro is costing but all the same he is an excel stalwart, and prefers pivot tables, which I hate. I am visually driven and don't visualise data from the numbers. 

The originator data comes from our management system which although costs a lot of money, does not even come close to the visual power PBI does thats why I use it. but I want to learn more, and have tried searching and attempting on google and the community all day alas to no effect.

Thanks for all the help

[MFelix]

Hi @AndyTilley ,

I understand what you say about pivot tables, I used to say to my previous boss that she had to do at least one pivot table a day or she would go crazy, I spend 3 years convincing her to drop excel (especially pivot tables) and used PBI but it worked.

I've been looking at your data and just to be sure of one thing, your CSV file as the same stucture of both tables that the pivot tables use for the chart correct?

Only changes is that one as the start week and another the end week? 

Can I use the excel file joining both datasource for the pivot as your source in PBIX file?

Asking this because in your datasource there is no closed dates filled so to calculate the difference between open and close is not possible since everything is open.

[AndyTilley]

Hi Miguel - sorry for the long reply

I use the master regional analysis and the open ticket data files only for my data. the quoted repair sheet was to illustrate and give a reverse engineering route and wont be used in my everyday  - does that help you?

When my boss put it together he used two different files for the data input like you say to get the open and closed.

I have never gone so far yet to use relationships from two data files, I am not that confident yet.

My system is messy but its the only way I have figured it out as there is no database function output on our system that says "job closed" its a button we press to get the data on screen. over two years running this system we have over 30,000 jobs on it, only 850+ at this time are open jobs, requiring us to return and repair or fit parts.

so it involves two different file outputs. Both output as CSV , except the Master analysis I copy/paste the CSV details in, as there are masses of calculations done at the end of the excel sheet that I cannot figure out how to do in DAX yet. (I am learning this in my spare time)

I use the PBIX file to visualise - using the CSV file (directly exported from our system) to populate the data. This is because there is a switch on our system to show open and closed jobs. This way I can see how many open jobs there are to do and what category they come under, whether they are urgent, and have parts available to do the work.

My boss gave us the "quoted repairs backlog" spreadsheet that had the chart data I would like to visualise.

You are correct that the CSV file has no closed dates as above, the output from our system is purely open jobs. I tagged in the master regional analysis as that has the closed dates, where the CSV doesnt. It downloads from two different places on the system and is incredibly complex to work with. we can only dump CSV files with the columns as specifed.

The problem with the master regional analysis is that it records every instance of the ticket. our system has a ticket number and work unit. if one engineer attends its one work unit. if he attends twice, two units, if two engineers go on two days, four units etc. so you get a repeat of the work.reference column.  

I am not that good with DAX and knew how to do most of the calculations in Excel so I made a new column to state if the number was unique in column AO.

I don't know how to slice it in this case to show if a job is open or closed in PBI, hence two data files.

All the files are saved on a central drive which I update and copy into. 

[MFelix]

Hi @AndyTilley 

Sorry for returning with more questions, but as you say the information is complex.

I need to understand what are the files used for the pivot tables or the based of that information. Are you abble to provide both CSV files that you use to make the join between the open and closed? 

You refer there are two files as base and if they are similar PBI does the match by itself, if they are different you can standardize them and join  the columns.

Can you share some more information on the original base files and the steps you go trough to get the information handled in Excel?

Once again sorry for asking more question but just want to provide a full answer so that you don't have issues in the future when adding additional information.

[AndyTilley]

Hey Miguel,

Sorry, in advance I know this is a mess, what I am really trying to do is simplify it, honest  and be ahead of my boss when he comes stomping into my office demanding answers.

The original data for the pivots is in the Quoted repairs spreadsheet

What I do now, is only show how many repairs are open to do.

What I want to show is the charts at the bottom there of how well we are doing. Are the repairs going up or down. Are we making good headway?

All the above is from the file given by my boss

now

I use my PBIX file [open tickets analysis] to display the visuals on only open tickets. the data I get this from is [open ticket data.csv]. I can only generate this either open or closed.

The [Master regional analysis] I also use to display tons of information in another PBIX report. however it holds the same data I believe as the sheet above with open and closed dates. I dont have access to the original CSV files for the [quoted repairs.xlsx]

my plan was to use the [open ticket data.csv] to show open tickets (remember can only see open or closed) and the [master regional analysis.xlsx] for the secondary data to show the PBI report on how we are doing as a team.

I have uploaded the master analysis PBI file so you can see how I use the data from the [master regional analysis.xlsx].

I want to use that same data in the open tickets analysis report. Im just too young on PBI and frustrated as I cannot do what I know I can do in excel. I know there is a way, but I have tried all I can to get this to work how I want and I am sure its an easy fix with DAX, I just don't know enough.

[MFelix]

Hi @AndyTilley ,

Sorry once again for not giving you a definitive answer yet, but I need some information about projects with completion date filled in.

Looking at your PBIX file the results you have for open projects are ok, you can add some addtional values for months or weeks, but it's weel made.

Can you share a file with close dates? Or should I take them from one of the other files you provide? Can I make a new source file from the 2 tables in the excel that have start and end date?

Once again I'm sorry for all this questions but as a best practice if your overlook the data part all your charts will be messed up.

So better to invest in making the database correct now than to have to work it in the future.

[AndyTilley]

Hey Miguel,

Thanks for your continued assistance.

It is probably easiest if you create from the files I have there as that is what I work with on a regular basis.

The final outcome really is to see how we are progressing along the way with the many variables in play. I am at a (frustrating) loss on a way forward and if you did something like this I would be not only grateful, but would want to know the ins and outs of how its done. I am an engineer by trade so I am very inquisitive and don't stop unless I have the answer.

I don't know what else I can give you as I use just the Master analysis and the associated PBI file. the data from the master analysis should have closed dates in if the job is done.