Data Modeling - Line Chart

[noemietl]

Hello,

I am fairly new using Power BI and am not able to get a chart representing the data I want.

So, I have different type of orders (online, in-store, etc) and each order has an order number. Each order number is associated with tasks. For example, an online order has 5 tasks (5 lines of data) such as (2h packing - 1h mailing - etc.) All my orders also have an order date.

I am trying to use the line chart to represent the average work time (tasks time) per order type, per year. For example, the average time required for online orders vs for in-store orders, and see the progression each year.

I put the following data in the following box;

X ; Year

Y ; Time of tasks

Legend ; Order type

My problem is, if I select (Average) ''Time of tasks'' it is giving me the average of all lines together (which is low, as all tasks take between 1-3h). I want the average per order number and order type; I want Power BI to give me the average time it takes for each order (not for each task seperately) which should be between 8-10h per order.

Not sure if I am being clear... thanks so much for anyone that will take the time to read and help!

[amitchandak]

@noemietl , I think is is case avg of sum

Try like

Sumx(Values(Table[Task]), calculate(Sum(Table[Time Value])) )

Avg of Sum : https://youtu.be/cN8AO3_vmlY?t=22980

[noemietl]

Hello,

thank you for your reply. It does not seem to be working.

Here is my formula 

Avg of task cost per order type = AVERAGEX(VALUES(MasterData[OrderType]),CALCULATE(SUM(MasterData[Hours of work])))

X ; year

Y ; the formula

Legend ; Order type

According to my data, the numbers are not matching if I do a manual calculations.

Anything you think I am doing wrong?

Thanks