Dividing columns that need to be counted and sorted by date

[slounsbury]

Hi All,

I am trying to build an efficency chart to show the count of configs divided by total hours of all configs. This is being used to compare are different customers. The issue I am having is being able to divide the count and sum of two columns while also only showing the past 10 days. Here is what my table looks like:

Config_RecID	SR_Service_RecID	Hours_Total	Config_Name	Company_Name	Company_RecID	Service_Entered_Date	Status
1	100	2	TOC32	Company A	100	1/11/18	Active
2	101	4	LAP-VIKI	Company B	200	1/12/18	Active
3	102	2.78	CR-TAMI	Company C	300	1/13/18	Active
4	103	0.15	KHSCLT07	Company D	400	1/14/18	Active
1	104	0.75	TOC32	Company A	100	1/15/18	Active
1	105	1	TOC32	Company A	100	1/16/18	Active
2	106	2	LAP-VIKI	Company B	200	1/17/18	Active
4	107	0.5	KHSCLT07	Company D	400	1/18/18	Disabled
4	108	0.5	KHSCLT07	Company D	400	1/19/18	Active
4	109	0.5	KHSCLT07	Company D	400	1/20/18	Disabled
3	110	0.25	CR-TAMI	Company C	300	1/21/18	Active
1	111	0	TOC32	Company A	100	1/22/18	Active
2	112	0	LAP-VIKI	Company B	200	1/23/18	Disabled
4	113	3	KHSCLT07	Company D	400	1/24/18	Active
1	114	1.25	TOC32	Company A	100	1/25/18	Active
1	115	2.25	TOC32	Company A	100	1/26/18	Active

Here is a chart of what I am looking for, the columns in bold are what I am wanting to create. On the efficency column I would also like to make it only calucated the values from 1/16/18 to 1/26/18 for example.

Config_RecID	SR_Service_RecID	Hours_Total	Config_Name	Company_Name	Company_RecID	Service_Entered_Date	Status	Config_Count	Efficency
1	100	2	TOC32	Company A	100	1/11/18	Active	6	0.8
2	101	4	LAP-VIKI	Company B	200	1/12/18	Active	3	0.6
3	102	2.78	CR-TAMI	Company C	300	1/13/18	Active	2	0.66
4	103	0.15	KHSCLT07	Company D	400	1/14/18	Active	5	1.08
1	104	0.75	TOC32	Company A	100	1/15/18	Active	6	0.8
1	105	1	TOC32	Company A	100	1/16/18	Active	6	0.8
2	106	2	LAP-VIKI	Company B	200	1/17/18	Active	3	0.6
4	107	0.5	KHSCLT07	Company D	400	1/18/18	Disabled	5	1.08
4	108	0.5	KHSCLT07	Company D	400	1/19/18	Active	5	1.08
4	109	0.5	KHSCLT07	Company D	400	1/20/18	Disabled	5	1.08
3	110	0.25	CR-TAMI	Company C	300	1/21/18	Active	2	0.66
1	111	0	TOC32	Company A	100	1/22/18	Active	6	0.8
2	112	0	LAP-VIKI	Company B	200	1/23/18	Disabled	3	0.6
4	113	3	KHSCLT07	Company D	400	1/24/18	Active	5	1.08
1	114	1.25	TOC32	Company A	100	1/25/18	Active	6	0.8
1	115	2.25	TOC32	Company A	100	1/26/18	Active	6	0.8

On the report side I am wanting to create a chart that looks like this.

Company_Name	Efficiency
Company A	0.8
Company B	0.6
Company C	0.66
Company D	1.08

I have tried several different mesaures but was unable to get specific numbers for each company and it would just show an overall efficency of everything. Any help would be much appreicated! I've been working on this for some time and haven't really got anywhere.

[v-jiascu-msft]

Hi @slounsbury,

Could you please post the steps to get "Efficiency"? I would suggest you create the "Efficiency" as a measure rather than a calculated column.

Best Regards,

Dale