Average

[Laska02]

Hello,

I have problems getting the right average. 

It's about customer counters where I have several measurements every day. I would like to have an average per weekday (Monday, Tuesday, etc) for an average week.

I have some data:

- date 

- time

- values

See table below

The table is:

If I now create a 2nd table in which I let PowerBi determine the averages, it goes wrong. PowerBi divides the number of customers by the product of the number of measurement moments and the number of days.

The average for Monday (daynumber 1), for example, should be 152.

Please advise, thanks in advance.

[rubayatyasmin]

Hi, @Laska02 

To average your customer count data by weekday in Power BI, you'll want to create a calculated column for the day of the week, and then use the AVERAGE function within the context of a visualization, not a new table.

Here are the steps you should follow:

1. Add a Calculated Column for Day of the Week:

   Click on the modeling tab in the Power BI Desktop and then on the "New Column" button.

   You'll want to use the WEEKDAY function to create a new column that represents the day of the week. The formula will look something like this:

   DayOfWeek = WEEKDAY('YourTable'[Date])

   This will return a number from 1 (for Sunday) to 7 (for Saturday), according to the default parameter. If you want to start with Monday as 1, modify the function as follows:

   DayOfWeek = WEEKDAY('YourTable'[Date], 2)

   You might also want to use the FORMAT function to return the actual name of the weekday:

   DayOfWeekName = FORMAT('YourTable'[Date],"dddd")

2. Use the AVERAGE Function in a Visualization:

   Once you have the Day of Week column, you can then create a visualization (like a bar chart) that shows the average values for each day of the week.

   Drag your "DayOfWeekName" column to the Axis area and your "values" column to the Values area. By default, Power BI may sum your values. You will need to click the dropdown arrow next to the field name in the Values area and select "Average" instead of "Sum".

   You should now see a chart that gives the average customer count for each day of the week.

[Laska02]

Thank you for your response, I've been on vacation, hence the late response.

Unfortunately this is not the solution.
I have already applied the method you recommend and also incorporated it into the example, this is table 2.
For Monday, PowerBi then divides the number of customers by the product of the number of Mondays and the number of measurement moments).

Do you have another idea?

[rubayatyasmin]

@Laska02 

If you are finding that the averages calculated by PowerBI are not matching the expected averages, it could be because of the way PowerBI handles grouping in aggregations.

One thing that could be causing the issue you're describing is if PowerBI is first calculating an average for each Monday (and for each time slot on Monday, if your data has multiple records per Monday), and then calculating an average of these averages.

To avoid this, you need to create a new calculated column that represents the total number of customers for each day. This will ensure that the average calculated later on is accurate. Here's how you could do it:

1. Create a New Calculated Column for Day Total

   DayTotal = CALCULATE(SUM('YourTable'[Values]), ALLEXCEPT('YourTable', 'YourTable'[Date]))

   This formula will sum the 'Values' column for all records that have the same date, effectively giving you the total number of customers for each day.

2. Create a Measure for Average Per Day

   AveragePerDay = AVERAGE('YourTable'[DayTotal])

   This will average the 'DayTotal' column, giving you the average number of customers per day.

3. Create a Visualization

   Now you can create a visualization like you did before, using the 'DayOfWeekName' column for the Axis and the new 'AveragePerDay' measure for the Values. This should give you the correct average per weekday.

Let me know if this solution helps, or if there are more specifics to your situation that I didn't account for, I'd be happy to help further.

[Peter32]

Thank you very much. This is the solution i was looking foor.

[rubayatyasmin]

@Peter32  good to know it helped. I took GPTs help to generate this much detailed info. Figured time saver. 

Thanks