Chart with averages ( including missing records )

[Gochix]

Hi there.

I'm coming for help to solve a problem I'm facing right now:

I would like to calculate the average score based on sharepoint list records and then with that information I would display the data as a Line Chart.

Why I'm struggling is the average will only calculate the value that's inside the SharePoint list.

If there is no record then nothing to calculate.

1 ) I have a list [AreaTitles] with "Area" that includes all "Area" titles possible and the "Zone" that represents color (Red,Blue,Green etc.) - Each "Zone" has different Area titles.

2) In the main List where the records are listed there is a "Day" columns and "Week" column. - Week will be used to filter the X axis for Line chart. ( 52 weeks so 52 labels ) , Day column will be used to calculate average for each day.

What I'm thinking is to somehow check weather there is a record for - Area (checks every week day Mon - Sun + Week 1 - 52) If there is no record found for Area title from [AreaTitles] then count it as 0 and add to collection so I can anyway calculate the average.

I've tried to use the formula that displays the Area Titles if there is no record for specific Date but tried to modify it and filter the Day and Week but unsuccessfully.

ClearCollect(
    AverageList,
    Filter(
        AddColumns(
            MissingAreas, // Area Titles list
            "temp",
            LookUp(
                'SPList',
                Area = MissingAreas[@Title] && Zone = "Green",
                Area
            )
        ),
        IsBlank(temp)
    )
)

Any help would be appreciated.

[rubin_boer · Accepted solution]

hi @Gochix 

"Why I'm struggling is the average will only calculate the value that's inside the SharePoint list.

If there is no record then nothing to calculate." - this is correct behavior.

Please consider the following:

• Average(2,2,2,2,2) = 2
• Average(2,2,2,2,0) = 1.6
• Average(2,2,2,2,Blank()) = 2 

If there is no value it will be treated as blank and the average will be n-1 or the number of blanks. 

You don't need to check for empty spaces to do the calculation, the second part of your question is important as 0 <> Blank().

In the event you wish to substitute blank with 0 you can do this:

Average(2,2,Coalesce(Blank(),0),2,2) //if you have a blank it is replaced with 0.

Consider this:

when you have your data there will be blanks like this

Add a column to replace the blanks with 0's

Now you can do your calculations.  

You can also consider replace the average with a sum and count calc

Hope it helps,

R

[Gochix]

Hi @rubin_boer ,

I might not explain my issue correctly.

The thing is if you look on the picture below then there is "Location" and then each day MUST have a score and if there is no score for that day then it will display as 0. And total for a week is then calculated.

This is Zone - "Red"

Then if we look on the Line chart then we can see that it only calculated the score for 2 cells and result is 80.

Then if I can formulate my question again is how to check for each location if there is a record in database for each day and each week knowing that there always must be a score to calculate the end result correctly.

Even if there is no record in the database then it would be a "0" for that day.

I'm sure it's a simple formula but I'm not that good in formulas.

[rubin_boer]

@Gochix  can you share what your data (directly from the source) look like for the three days?

In your chart, it appears the 0 are blanks. In your case, I would use the update column where "Coalesce(yourColum,0)"

I expect the data to look like this (A):

{Location, Shift, Monday, ...., Sunday, Week}

{"Live ... Yard", "Days", , , , , , ,100, , 14.3} 

or

It looks like this (B):

{Location, Shift, Day, Score}

{"Live ... Yard", "Days", "07 Aug 2022", 100} 

With either data structure (B is preferred), we can sort you out. 

Can you share the data structure you have at this type of level or how you would capture it in Excel for example.

Regards,

R

[Gochix]

Hi @rubin_boer ,

Please see the data structure.

As you see here as an example I only have 3 records.

But I would like to display in my chart all the rest Areas that are not inserted in the SharePoint and set the Score as 0. For each day,week. - Knowing all area names I could somehow make a new collection where all area names are listed. Then do ForAll(Patch if no record found for Monday....Sunday for week 1 then set each day score 0. And so on. Continue till week 52.

[Gochix]

I'm upping the question - maybe someone have the knowledge for this to help me out.

[Gochix]

Hi,

I finally had a minute to look on this with a clear head and sorted out what I needed.

The question would be is there a way how to implement "For Loop" in the formula below? 

Clear(NewChart);

////////////////// Week 1

ForAll(AreaTitles.AllItems,
Collect(NewChart,{Title:AreaTitle.Text,Zone:ZoneTitle.Text,Week:1,

Score:If(IsBlank(Sum(LookUp('5s-Summary',Area = AreaTitle.Text && Week = 30,Score))),0,Round(Sum(LookUp('5s-Summary',Area = AreaTitle.Text && Week = 1,Score)) / 7,1))

}));
////////////////// Week 2

AreaTitles.AllItems is a gallery that stores all possible Area Titles.

Everything works fine and it would make no difference if I would just copy paste now the same lines of code till Week 52.

But just to avoid a "Don't repeat yourself" thinking how to implement a for loop so the Week: would change from 1 till 52..

If anyone has any knowledge in this please let me know. I'll try of course to search more when I will have a minute. 

Sharing a step in case somebody need a similar solution in the future.