cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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:

 

chart.jpeg

 

The data would look something like this:

 

Instance IDAttribute 1Attribute 2Attribute 3Attribute 4Attribute 5Attribute 6Attribute 7Attribute 8Attribute 9Attribute 10
1YesNoNoYesYesYesYesNoYesNo
2NoYesYesYesYesYesYesYesYesYes
3NoYesYesYesYesYesYesYesYesYes
4YesYesYesYesYesYesYesYesYesYes
5YesYesNoNoYesNoNoYesYesNo
6YesNoYesYesYesYesYesYesNoYes
7NoNoYesYesNoYesYesYesYesYes
8YesYesYesNoYesYesYesYesYesYes

 

 

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 2
Microsoft
Microsoft

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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/17Communiqué 183Yes  Yes  NoYesYesYes
7/20/17Communiqué 250Yes  No  NoYesYesNo
7/25/17Communiqué 317No  No  NoNoYesNo
7/26/17Communiqué 4100Yes  Yes  YesYesYesYes
7/26/17Communiqué 567Yes  Yes  YesYesNoNo
7/27/17Communiqué 667Yes  Yes  NoYesYesNo
7/31/17Communiqué 767Yes  Yes  NoYesNoYes
8/8/17Communiqué 8100Yes  Yes  YesYesYesYes
8/20/17Communiqué 983Yes  Yes  YesYesYesNo
8/22/17Communiqué 1083Yes  Yes  YesNoYesYes
8/30/17Communiqué 11100Yes  Yes  YesYesYesYes
9/14/17Communiqué 12100Yes  Yes  YesYesYesYes
9/14/17Communiqué 1367No  Yes  YesNoYesYes
9/19/17Communiqué 1433No  No  YesNoNoYes
9/26/17Communiqué 15100Yes  Yes  YesYesYesYes
10/9/17Communiqué 16           
10/12/17Communiqué 1783Yes  Yes  NoYesYesYes
10/17/17Communiqué 1867Yes  Yes  NoNoYesYes
10/19/17Communiqué 1983Yes  Yes  YesNoYesYes
11/14/17Communiqué 2050No  Yes  NoNoYesYes
11/27/17Communiqué 21100Yes  Yes  YesYesYesYes
1/6/18Communiqué 2283Yes  Yes  NoYesYesYes
1/8/18Communiqué 2367Yes  Yes  NoNoYesYes
1/12/18Communiqué 2467No  Yes  YesNoYesYes
1/22/18Communiqué 25           
2/6/18Communiqué 26100Yes  Yes  YesYesYesYes
2/8/18Communiqué 27100Yes  Yes  YesYesYesYes
2/19/18Communiqué 28100Yes  Yes  YesYesYesYes
2/27/18Communiqué 29100Yes  Yes  YesYesYesYes
2/27/18Communiqué 30100Yes  Yes  YesYesYesYes
3/5/18Communiqué 31100Yes  Yes  YesYesYesYes
3/6/18Communiqué 3267Yes  Yes  NoNoYesYes
3/13/18Communiqué 3383Yes  Yes  NoYesYesYes
4/2/18Communiqué 34100Yes  Yes  YesYesYesYes

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors