Getting a YTD Average from Computed Monthly Scores

[cferrer]

Hi!

I am trying to get the YTD average after I get the monthly computed scores.

To illustrate:

Below is the raw data table:

response ID	Month	Category	Business
1	Jun-17	1	A
2	Jun-17	1	C
3	Jun-17	1	B
4	Jun-17	2	C
5	Jun-17	3	A
6	Jun-17	2	A
7	Jun-17	3	B
8	Jun-17	2	C
9	Jun-17	2	A
10	Jun-17	3	A
11	Jun-17	2	B
12	Jun-17	1	A
13	Jun-17	1	A
14	Jun-17	1	A
15	Jun-17	1	A
16	Jun-17	2	B
17	Jun-17	3	C
18	Jun-17	1	A
19	Jun-17	2	A
20	Jun-17	3	B
21	Jun-17	1	A
22	Jun-17	1	C
23	Jul-17	1	B
24	Jul-17	2	C
25	Jul-17	3	A
26	Jul-17	2	A
27	Jul-17	3	B
28	Jul-17	2	C
29	Jul-17	2	A
30	Jul-17	3	A
31	Jul-17	2	B
32	Jul-17	1	A
33	Jul-17	1	A
34	Jul-17	1	A
35	Jul-17	1	A
36	Jul-17	2	B
37	Jul-17	3	C
38	Jul-17	1	A
39	Jul-17	2	A
40	Jul-17	3	B

I'd like to compute it like so, grouping the responses by Month and Business, counting each response that is under Category 1, 2, 3. From there computing the Score as (Category 3 - Category 1) / Grand Total * 100. And then getting the average per Business.

I tried using Summarize and AverageX but kept getting multiple values/scalar value error messages.

Count of response ID		Category
Month	Business	1	2	3	Grand Total	Score ( ([3] - [1]) / Grand Total * 100 )
Jun-17	A	7	3	2	12	-           41.67
Jun-17	B	1	2	2	5	20.00
Jun-17	C	2	2	1	5	-           20.00
Jul-17	A	5	3	2	10	-           30.00
Jul-17	B	1	2	2	5	20.00
Jul-17	C		2	1	3	33.33
				Average	A	-           35.83
					B	20.00
					C	6.67
				[Average Score if A / B / C]

[Ashish_Mathur · Accepted solution]

Hi,

Here is the calculated field formula i used.  You may download my file from here.

=AVERAGEX(SUMMARIZE('Calendar','Calendar'[Year],'Calendar'[Month],"ABCD",(CALCULATE(COUNTROWS(Data),Data[Category]=3)-CALCULATE(COUNTROWS(Data),Data[Category]=1))/COUNTROWS(Data)),[ABCD])

[cferrer]

Thanks, Ashish_Mathur! This helps, is there a way to make a measure that just returns the -14.14% instead of doing it through a table? 🙂

[v-shex-msft]

Hi @cferrer,

Drag this measure into card visual and it will show the summarized result.

Regards,

Xiaoxin Sheng

[cferrer]

response ID Month Category Business 1 Jun-17 1 A 2 Jun-17 1 C 3 Jun-17 1 B 4 Jun-17 2 C 5 Jun-17 3 A 6 Jun-17 2 A 7 Jun-17 3 B 8 Jun-17 2 C 9 Jun-17 2 A 10 Jun-17 3 A 11 Jun-17 2 B 12 Jun-17 1 A 13 Jun-17 1 A 14 Jun-17 1 A 15 Jun-17 1 A 16 Jun-17 2 B 17 Jun-17 3 C 18 Jun-17 1 A 19 Jun-17 2 A 20 Jun-17 3 B 21 Jun-17 1 A 22 Jun-17 1 C 23 Jul-17 1 B 24 Jul-17 2 C 25 Jul-17 3 A 26 Jul-17 2 A 27 Jul-17 3 B 28 Jul-17 2 C 29 Jul-17 2 A 30 Jul-17 3 A 31 Jul-17 2 B 32 Jul-17 1 A 33 Jul-17 1 A 34 Jul-17 1 A 35 Jul-17 1 A 36 Jul-17 2 B 37 Jul-17 3 C 38 Jul-17 1 A 39 Jul-17 2 A 40 Jul-17 3 B

[Ashish_Mathur]

Hi,

Your data is not clear.  Paste the data in a proper Table format and also show the expected result.