Average of instances per weekday from data where every instance is a row

[Infopiste]

Hi,

I am fairly new to PBI.

I am importing my data from a SQL server into Pbi.

My data contains information about customer orders. Every order an unique row in my data with an ordernumber as the unique identifier. I also have the order date, weekday and a lot of other information about the order.

I would like to make a powerbi report showing the average amount of orders received per weekday calculated for all received customer orders in my data.

I just can not seem to get an average value per weekday as the average value in my data(rows) is 1, every row is one order. I have created a column converting every ordernumber into a number (1) (ISORDER = 1), representing the amount of orders in every row(did not help much).

When i use a measure of column to calculate COUNT(IS ORDER) and insert that into my report i get all the orders received per weekday, but not the average (how many orders do we receive during a specific weekday?). When i use AVERAGE(IS ORDER) i get the value 1, as every row in my data contains only 1 order.

Help please?

[v-yulgu-msft]

Hi @Infopiste,

If you add [WeekDay] field and [OrderNumber] into a table visual, choose "Count" as aggregate function for [OrderNumber], you would get the total amount order per weekday rather than1.

From current description, I cannot understand what the average value per weekday should be. Please provide some sample data and show us your expected result to make the requirement more clear. How to Get Your Question Answered Quickly

Best regards,

Yuliana Gu

[Infopiste]

Hi,

Thanks for your reply.

Yes that is correct, i get the count of the years total orders per weekday for the whole year.

My data contains orders from week 1 to week 48. Every week contains weekdays from 1-7. That is 48 x monday, 48 x Tuesday, 48 x wednesday and so on. We are receiving orders every weekday.

I would like to get the average amount of orders received per weekday. My data is however switched the other way around, i do not have a summarized amount of orders per weekday in my data. Every order is a row, in that row i have the weekday as one column. i have for example approx 10 000 orders received on a monday in my data, for the whole year.

I would like to gt it into a visual table showing

Monday = 480 (Average of amount orders received ón a monday calculated from all orders received on a monday from week one to current week.)

Tuesday = 500 (Average of amount orders received ón a tuesday calculated from all orders received on a tuesday from week one to current week.)

Wednesday = 452 (Average of amount orders received ón a wednesday calculated from all orders received on a wednesday from week one to current week.)

And so on. Hope this explains my problem more clearly.

[Infopiste · Accepted solution]

I found the solution myself!

I first had to distinctcount all dates to get the value for total amount of calenderdays represented in my data, then count ordernumbers and divide ordernumbers by distinctcount of calenderdays = Average of orders received per day. After that i used a table visual with values weekday and average orders received per day. That solved it for me.

Thanks anyway 🙂