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: DateOpen groupClosure groupSolvedTransferreddate/timegroup1group2 1date/timegroup1group11 date/timegroup1group3 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.

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.

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.

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 @TimezoneClose Time @TimezoneOpen GroupClosed GroupSolved Transferred29 mar 2016 07:50:5521 okt 2016 10:23:20James TeamJames Team1 29 mar 2016 07:50:5521 okt 2016 10:23:20James TeamMikes Team 129 mar 2016 07:50:5521 okt 2016 10:23:20James TeamJames Team1 21 apr 2016 16:41:4824 okt 2016 12:13:04James TeamMikes Team 121 apr 2016 16:41:4824 okt 2016 12:13:04James TeamJames Team1 21 apr 2016 16:41:4824 okt 2016 12:13:04James TeamJames Team1 2 maj 2016 14:15:4521 okt 2016 10:21:20James TeamJames Team1 2 maj 2016 14:15:4521 okt 2016 10:21:20James TeamMikes Team112 maj 2016 14:15:4521 okt 2016 10:21:20James TeamJames Team1 11 jul 2016 13:45:0221 okt 2016 10:20:52James TeamJames Team1 11 jul 2016 13:45:0221 okt 2016 10:20:52James TeamJames Team1 11 jul 2016 13:45:0221 okt 2016 10:20:52James TeamMikes Team 17 sep 2016 00:03:1621 okt 2016 10:23:08James TeamMikes Team 17 sep 2016 00:03:1621 okt 2016 10:23:08James TeamMikes Team 17 sep 2016 00:03:1621 okt 2016 10:23:08James TeamMikes Team 17 sep 2016 22:24:4924 okt 2016 12:12:30James TeamJames Team1 7 sep 2016 22:24:4924 okt 2016 12:12:30James TeamJames Team1 7 sep 2016 22:24:4924 okt 2016 12:12:30James TeamJames Team1 14 okt 2016 09:48:2521 okt 2016 10:18:06James TeamJames Team1 14 okt 2016 09:48:2521 okt 2016 10:18:06James TeamJames Team1 14 okt 2016 09:48:2521 okt 2016 10:18:06James TeamJames Team1 14 okt 2016 10:34:279 nov 2016 15:25:06James TeamJames Team1 14 okt 2016 10:34:279 nov 2016 15:25:06James TeamJames Team1

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?

Counting Solution rate per Date in [massive file]

11 Replies

Anonymous

Not applicable

7 years ago

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

Helper I

7 years ago

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

Anonymous
Not applicable
7 years ago
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.

Forum Discussion

Counting Solution rate per Date in [massive file]

11 Replies

Related content

Counting nulls

DAX SOLUTION NEEDED

Showing 0 count in column chart - solutions not working, ideas?

Predictive analytics solution

Modeling Solution?

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