Forum Discussion
Comparing historical average to current week average
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
2 Replies
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
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
