cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
niklasstrauch Frequent Visitor
Frequent Visitor

Customer Service Dashboard: Created Tickets, Closed Tickets, Open Tickets combined with Slicer

Dear all, 

I have an issue creating a customer service dashboard and also watching multiple youtube tutorials didnt help me: 

I have an excel sheet offering 3 columns: Ticket created (date), Ticket closed (date), Ticket status (closed, pending, in work, blank)

I created a day slicer which lets me select any kind of date range. Now my goal is to create 3 KPIs for the respective date ranges I select in the Slicer:  
1. # of tickets created
2. # of tickets closed

3. # of open tickets 

I already created a separate date table. Thereby, I managed to create the KPI '# of tickets created' but the other two are not possible. I guess especially the third KPI requries some calculation? 

e.g.:
@Greg_Deckler : I saw that you are quite familiar with the topic. Would you be so nice and could help me pleease? Would be such a help! 

Thank you very much and best regards!
Nik

1 ACCEPTED SOLUTION

Accepted Solutions
Anonymous
Not applicable

Re: Customer Service Dashboard: Created Tickets, Closed Tickets, Open Tickets combined with Slicer

To create this simply make a measure calculating the difference between Opened and Closed. 

 

Use the modeling tab to create a new measure. I used the following code to create the proper outcome.

Measure = COUNT(Opened[Opened]) - COUNT(Closed[Closed])

The outcome is like this:

mesure.png

I hope your dashboard can be finished now! Smiley Happy

8 REPLIES 8
v-danhe-msft Super Contributor
Super Contributor

Re: Customer Service Dashboard: Created Tickets, Closed Tickets, Open Tickets combined with Slicer

Hi @niklasstrauch ,

Could you please post some simple sample data and your desired result to have a test if possible?

You could see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
niklasstrauch Frequent Visitor
Frequent Visitor

Re: Customer Service Dashboard: Created Tickets, Closed Tickets, Open Tickets combined with Slicer

Here is the sample Data: 

https://drive.google.com/file/d/1RER2T34ybjpRX6jrTGuxOf0_Ifzwq0K2/view?usp=sharing

When selecting 01.10.2018-31.10.2018, there should be 393 created tickets (works) and 241 closed ones. I have a problem creating the 'closed tickets' KPI and the 'open ticket' one. 

Thank you very much for your help!! 

Best, 
Nik

Anonymous
Not applicable

Re: Customer Service Dashboard: Created Tickets, Closed Tickets, Open Tickets combined with Slicer

Hi,

You need to create 2 different tables to get this to work. Because the relationship with date can only affect either Closed or Opened.

To create the new tables you can use the following code when creating a new table

Opened = 
    SELECTCOLUMNS (
        Sheet1,
        "Ticket Number", Sheet1[Ticket number],
        "Opened", Sheet1[Opened]
        )

and

Closed = 
    SELECTCOLUMNS (
        Sheet1,
        "Ticket Number", Sheet1[Ticket number],
        "Closed", Sheet1[Closed ]
        )

Then create the relationship as follows:

relationships.png

Also, dont forget to chance the tiles to the correct opened and closed. As seen below.dash.pngAnd here you can find the example i made.

https://1drv.ms/u/s!AgnVgd15_ye0hBfLUybHR65dPEvE

Goodluck!

niklasstrauch Frequent Visitor
Frequent Visitor

Re: Customer Service Dashboard: Created Tickets, Closed Tickets, Open Tickets combined with Slicer

Awesome, thank you very much!! It worked. 

Do you also have a solution for the issue of creating the 'Open Tickets' KPI? I do not know how to create it, as its a bit more complex than the 'created' and 'solved' ones. 

Best, 
Nik

Anonymous
Not applicable

Re: Customer Service Dashboard: Created Tickets, Closed Tickets, Open Tickets combined with Slicer

With Open Tickets i think you mean the one on the status in work? 

As i do not have sample data with the 4th column i cannot create a preview for you, but if you use the Opened card and add a visual filter on the column status you can filter on that.

 

Note; for this to work you need to change this code:

Opened = 
    SELECTCOLUMNS (
        Sheet1,
        "Ticket Number", Sheet1[Ticket number],
        "Opened", Sheet1[Opened]
        )

and add the 4th column "status"

Opened = 
    SELECTCOLUMNS (
        Sheet1,
        "Ticket Number", Sheet1[Ticket number],
        "Opened", Sheet1[Opened],
        "Status", Sheet1[Status]
        )

Goodluck!

niklasstrauch Frequent Visitor
Frequent Visitor

Re: Customer Service Dashboard: Created Tickets, Closed Tickets, Open Tickets combined with Slicer

Hi @Anonymous , thank you! 

But I do not have another status column. Rather, I would like to count the tickets, which are created but not closed yet during the respective time range selected in the slicer. I guess it would have to be somehow dynamic? Could you help me with that? Highly appreciated! Thank you! 

Best, 
Niklas 

Anonymous
Not applicable

Re: Customer Service Dashboard: Created Tickets, Closed Tickets, Open Tickets combined with Slicer

To create this simply make a measure calculating the difference between Opened and Closed. 

 

Use the modeling tab to create a new measure. I used the following code to create the proper outcome.

Measure = COUNT(Opened[Opened]) - COUNT(Closed[Closed])

The outcome is like this:

mesure.png

I hope your dashboard can be finished now! Smiley Happy

niklasstrauch Frequent Visitor
Frequent Visitor

Re: Customer Service Dashboard: Created Tickets, Closed Tickets, Open Tickets combined with Slicer

Ah of course! Perfect, thank you very much for your time! :-)

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 44 members 1,045 guests
Please welcome our newest community members: