Comparing historical average to current week average

[zallebban]

I have been experimenting with Power BI and can handle most simple visualizations, but this one is beyond my skill.  I've researched the archives and have not found quite the same scenario.

Currently, I am measuring quality of a product by way of 10 attributes.  These get Yes/No assessments in the SharePoint list where this data lives, then SP calculates a 0-100 average across all attributes.  So each product instance gets one quality score at the end of the day.

I am trying to get more granular.  I'll do this by charting historical average (excluding the current week) for each attribute as well as current week average.  As an idea, historical average could be displayed as a bar, with the current week average plotted as a point.  Something like this:

The data would look something like this:

Instance ID	Attribute 1	Attribute 2	Attribute 3	Attribute 4	Attribute 5	Attribute 6	Attribute 7	Attribute 8	Attribute 9	Attribute 10
1	Yes	No	No	Yes	Yes	Yes	Yes	No	Yes	No
2	No	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
3	No	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
4	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
5	Yes	Yes	No	No	Yes	No	No	Yes	Yes	No
6	Yes	No	Yes	Yes	Yes	Yes	Yes	Yes	No	Yes
7	No	No	Yes	Yes	No	Yes	Yes	Yes	Yes	Yes
8	Yes	Yes	Yes	No	Yes	Yes	Yes	Yes	Yes	Yes

If Power BI cannot do this with Yes/No data, I can convert to a 0-100 scale within SharePoint (although excluding the current week will be a trick I'll need to learn) and bring the data in as a 0-100 score for each value (or I will simply capture the input as 0-100 at the point of entry... will try to make that process change if it will ease the calculations).

Hope that makes sense.  Any guidance would be great.  Thanks!

Zayd

[v-jiascu-msft]

Hi @zallebban,

Please share a dummy sample of the original data. Because the sample you post doesn't contain the date column. Besides, there seems no such visual. How about the Line and clustered column visual?

Best Regards,

Dale

[zallebban]

Dale, I think that visual would work.

This should do it.  My post preview is showing the empty QC columns, but they are disappearing when I post.  For demonstration purposes, feel free to reduce the number of quality attribute columns (prefixed by "QC") to make things easier.  If necessary, I can send an Excel export if you provide an email address.  Thanks.

Zayd

Date InitiatedName of CommuniquéQuality ScoreQC: Aligns w/TemplateQC: ClarityQC: CohesionQC: CompletenessQC: ConsistencyQC: ContentQC: DateQC: Draft LabelQC: FormattingQC: Spelling/Grammar

7/14/17

Communiqué 1

83

Yes

Yes

No

Yes

Yes

Yes

7/20/17

Communiqué 2

50

Yes

No

No

Yes

Yes

No

7/25/17

Communiqué 3

17

No

No

No

No

Yes

No

7/26/17

Communiqué 4

100

Yes

Yes

Yes

Yes

Yes

Yes

7/26/17

Communiqué 5

67

Yes

Yes

Yes

Yes

No

No

7/27/17

Communiqué 6

67

Yes

Yes

No

Yes

Yes

No

7/31/17

Communiqué 7

67

Yes

Yes

No

Yes

No

Yes

8/8/17

Communiqué 8

100

Yes

Yes

Yes

Yes

Yes

Yes

8/20/17

Communiqué 9

83

Yes

Yes

Yes

Yes

Yes

No

8/22/17

Communiqué 10

83

Yes

Yes

Yes

No

Yes

Yes

8/30/17

Communiqué 11

100

Yes

Yes

Yes

Yes

Yes

Yes

9/14/17

Communiqué 12

100

Yes

Yes

Yes

Yes

Yes

Yes

9/14/17

Communiqué 13

67

No

Yes

Yes

No

Yes

Yes

9/19/17

Communiqué 14

33

No

No

Yes

No

No

Yes

9/26/17

Communiqué 15

100

Yes

Yes

Yes

Yes

Yes

Yes

10/9/17

Communiqué 16

10/12/17

Communiqué 17

83

Yes

Yes

No

Yes

Yes

Yes

10/17/17

Communiqué 18

67

Yes

Yes

No

No

Yes

Yes

10/19/17

Communiqué 19

83

Yes

Yes

Yes

No

Yes

Yes

11/14/17

Communiqué 20

50

No

Yes

No

No

Yes

Yes

11/27/17

Communiqué 21

100

Yes

Yes

Yes

Yes

Yes

Yes

1/6/18

Communiqué 22

83

Yes

Yes

No

Yes

Yes

Yes

1/8/18

Communiqué 23

67

Yes

Yes

No

No

Yes

Yes

1/12/18

Communiqué 24

67

No

Yes

Yes

No

Yes

Yes

1/22/18

Communiqué 25

2/6/18

Communiqué 26

100

Yes

Yes

Yes

Yes

Yes

Yes

2/8/18

Communiqué 27

100

Yes

Yes

Yes

Yes

Yes

Yes

2/19/18

Communiqué 28

100

Yes

Yes

Yes

Yes

Yes

Yes

2/27/18

Communiqué 29

100

Yes

Yes

Yes

Yes

Yes

Yes

2/27/18

Communiqué 30

100

Yes

Yes

Yes

Yes

Yes

Yes

3/5/18

Communiqué 31

100

Yes

Yes

Yes

Yes

Yes

Yes

3/6/18

Communiqué 32

67

Yes

Yes

No

No

Yes

Yes

3/13/18

Communiqué 33

83

Yes

Yes

No

Yes

Yes

Yes

4/2/18

Communiqué 34

100

Yes

Yes

Yes

Yes

Yes

Yes