Counting Solution rate per Date in [massive file]

[CauseAndEffect]

Hello! Im trying to create an historical dashboard for our internal Service desk.

Trying to figure out the best way to show our solution rate in % per day/week/month/year.

This is a simplified version of my table:

Date	Open group	Closure group	Solved	Transferred
date/time	group1	group2		1
date/time	group1	group1	1
date/time	group1	group3		1

*Date and time ranges from 2016-2019, all days of the year, all contained in one table.

Any tips or idéas on how to achive a solution rate column that works with the drilldown function in the report dashboard. 

[cnweke]

1) I'd make generate a (calculated) table for time: https://kohera.be/blog/power-bi/how-to-create-a-date-table-in-power-bi-in-2-simple-steps/

2) After that make a relationship between date from your table and date from the calculated table.

3) Make a time hierarchy (Y/M/W/D) or whatever you prefer.

4) SolutionRate: count calculate(count(Solved), Solved ="1") / count(solved)  or without the "" if it's really an integer. Count solved is just the total of all records.

5) Plot solution rate against the hierarchy dimension you just made

[CauseAndEffect]

Thanks alot! i will try that, do i need to create the Date table and hierarcy even if the time already exists within the table? See a more detailed version of the table?

Open Time @Timezone	Close Time @Timezone	Open Group	Closed Group	Solved	Transferred
29 mar 2016 07:50:55	21 okt 2016 10:23:20	James Team	James Team	1
29 mar 2016 07:50:55	21 okt 2016 10:23:20	James Team	Mikes Team		1
29 mar 2016 07:50:55	21 okt 2016 10:23:20	James Team	James Team	1
21 apr 2016 16:41:48	24 okt 2016 12:13:04	James Team	Mikes Team		1
21 apr 2016 16:41:48	24 okt 2016 12:13:04	James Team	James Team	1
21 apr 2016 16:41:48	24 okt 2016 12:13:04	James Team	James Team	1
2 maj 2016 14:15:45	21 okt 2016 10:21:20	James Team	James Team	1
2 maj 2016 14:15:45	21 okt 2016 10:21:20	James Team	Mikes Team	1	1
2 maj 2016 14:15:45	21 okt 2016 10:21:20	James Team	James Team	1
11 jul 2016 13:45:02	21 okt 2016 10:20:52	James Team	James Team	1
11 jul 2016 13:45:02	21 okt 2016 10:20:52	James Team	James Team	1
11 jul 2016 13:45:02	21 okt 2016 10:20:52	James Team	Mikes Team		1
7 sep 2016 00:03:16	21 okt 2016 10:23:08	James Team	Mikes Team		1
7 sep 2016 00:03:16	21 okt 2016 10:23:08	James Team	Mikes Team		1
7 sep 2016 00:03:16	21 okt 2016 10:23:08	James Team	Mikes Team		1
7 sep 2016 22:24:49	24 okt 2016 12:12:30	James Team	James Team	1
7 sep 2016 22:24:49	24 okt 2016 12:12:30	James Team	James Team	1
7 sep 2016 22:24:49	24 okt 2016 12:12:30	James Team	James Team	1
14 okt 2016 09:48:25	21 okt 2016 10:18:06	James Team	James Team	1
14 okt 2016 09:48:25	21 okt 2016 10:18:06	James Team	James Team	1
14 okt 2016 09:48:25	21 okt 2016 10:18:06	James Team	James Team	1
14 okt 2016 10:34:27	9 nov 2016 15:25:06	James Team	James Team	1
14 okt 2016 10:34:27	9 nov 2016 15:25:06	James Team	James Team	1

[cnweke]

Yeah, it's advisable since it gives you the ability to view your data for the entirety of 2016, 17, 18 and 19 next to each other. You can then use drill down features to lower the granularity to months, weeks, days. 

[CauseAndEffect]

Thanks alot!!!

Could you explain step 3-5(figured out step 3) 4-5 in more detail?

Also should i make a one to many hierarcy such as Date 1->* Statistics data?

[cnweke]

Yeah, no problem

2) The relationship between statistics and the new date table you created is one to many indeed. * - 1. One date in your date table can have several dates in statistics.

3) In Power BI client you can right-click a column (Year in this case) and then click create hierarchy.

What you should do next is just drag the following levels under it.

In this case Year -> MonthYear -> WeekNumber -> day or just Year -> month -> week -> day. The former is better if you have those columns.

4) Make a measure in statistics with the formula I gave you (or a slight variant). 

Try count calculate(count(Solved), Solved ="1") / count(solved) first.

If it fails try count calculate(count(Solved), Solved =1) / count(solved)

What this essentially does is it counts the amount of rows that have are solved. It then divides it by the total amount of rows you have. The "1" is in brackets because Dax makes a distinction between integers (numbers) and strings (text). Try both to see whichever works for you.

5) Add your measure as a bar chart under value and add the hierarchy as category with closure or open group as legend whatever works best.  You'll see various arrows on the visuals header, the first one allows you to 'zoom in on click ' i.e. go from year to month to week to day whenever you click a bar. The next to buttons are slightly harder to explain, just press them they're super intuitive.

[CauseAndEffect]

Awesome! i think i've got something, unsure if its correct.

This is what my Measurement looks like

CALCULATE(COUNT('Giant Document'[Solution]); 'Giant Document'[Solution]=1) / COUNT('Giant Document'[Solution])

Seems that i cannot follow your syntax properly.

The hierarcy works but the "solution rate" value is just 1.

Thanks alot for your help so far! 🙂

[cnweke]

Are you sure you've got the right decimals? Try with "1" too.

[CauseAndEffect]

Thanks for the tip! Tried both 1 and "1", and the column is formatted as "whole number".

Strange indeed.

[cnweke]

CALCULATE(COUNT('Giant Document'[Solution]); ('Giant Document'[Solution]=1)) / COUNT('Giant Document'[Solution])

 Could you add those 2 brackets in red? If it still fails could you please screenshot the error message you're getting? 

[CauseAndEffect]

Added but it still did not work, heres what i did:

SolutionRate = CALCULATE(COUNT('Giant Document'[TicketCount]); ('Giant Document'[Solution]=1)) / COUNT('Giant Document'[TicketCount])

Ticket count is just 1 per row in the table, representing 1 ticket.

Now the value shows a decimal number that could be the solution rate.

How do i change this number into a %?

Also the date hierarcy did not work out great for me it seems, i'm now using the standard date heirarcy that is created for the date table by default.

[cnweke]

Aha! That decimal number is (or rather should be) your solution rate. Just add it to a Power BI visual, if you add it and click on the little arrow most of them have a 'setting' where you can specify that it is in fact a percentage that you're adding.

If you can't find it just multiply by 100 and you're there either way.