Conditional average

[UlliJ]

I am quite new to powerBI and I feel like I have tried everything to solve this. 

The table below indicates time spent producing an order (Behandlingstid = total working days). As you can see every row indicates a product, but an order can extend beyond one row depending on no of products (the products don't necessarily have to be the same, that is just a coincidence).

I want the average of total working days pr order, but I just simply cannot figure out how.

Kind regards,

Ulrikke

[Phil_Seamark]

Hi @UlliJ

So do you want to sum up the Behandlingstid for each row per order.  And then work out the average across all orders?  Or are you after an an average of the rows within the order?

[UlliJ]

Hi @Phil_Seamark

Thank you for your fast response.

I want the average pr order so I can calculate the average for all orders, not just the average per row. Does that make sense?

[Phil_Seamark]

Can you please cut/paste some sample data to save having to type it into a local copy of PBIX 🙂

[UlliJ]

This is how new I am to this that I am not quite sure what you mean, but reading your question again it is the latter part I want. "Or are you after an an average of the rows within the order?"

Ulrikke

[Phil_Seamark]

Hi @UlliJ

Nah, what I meant was to provide some data in the form of text rather than a screenshot of an image,  What makes it easier to suggest formulas for you to use is to try and simulate your model as much as possible here.  I type in to a new PBI Desktop file your data but that takes too long and is prone to error.  🙂

[UlliJ]

Hi @Phil_Seamark,

Will this help? Never mind the time, I am interested in working days (which is the behandlingstid). As you can se each order has several products lines. These are completed at several different times. I want the average pr order, so that I again can find the average pr week, month etc usind DISTINCTCOUNT for order. Any suggestions? 

Opprettet dato	Oppr. kl	Ferdigm.dato	Ferdigm.kl.	Ordrenr	Produktnr	Ferdigmeldt	Totalt antall	Behandlingstid
03.01.2017	13:38	04.01.2017	12:47	353241	198101	3	3	2
03.01.2017	13:38	04.01.2017	12:47	353241	235100	1	1	2
03.01.2017	13:38	04.01.2017	12:47	353241	628100	2	2	2
03.01.2017	13:38	04.01.2017	12:47	353241	672191	2	2	2
03.01.2017	13:38	04.01.2017	12:47	353241	656100	1	1	2
03.01.2017	13:58	11.01.2017	09:17	353243	555002	1	1	7
03.01.2017	13:58	11.01.2017	09:17	353243	532100	1	1	7
03.01.2017	13:58	11.01.2017	09:17	353243	555003	1	1	7
03.01.2017	13:58	11.01.2017	09:17	353243	153100	1	1	7
03.01.2017	13:58	11.01.2017	09:17	353243	398100	10	10	7
03.01.2017	13:58	11.01.2017	09:17	353243	091301	5	5	7
03.01.2017	13:58	11.01.2017	09:17	353243	091101	20	20	7
03.01.2017	13:58	11.01.2017	09:17	353243	091146	5	5	7
03.01.2017	13:58	18.01.2017	14:29	353243	657100	5	5	12
03.01.2017	13:58	11.01.2017	09:17	353243	537100	25	25	7
03.01.2017	13:58	11.01.2017	09:17	353243	532100	25	25	7
03.01.2017	13:58	11.01.2017	09:17	353243	626000	30	30	7
03.01.2017	13:58	11.01.2017	09:17	353243	361100	1	1	7
03.01.2017	13:58	11.01.2017	09:17	353243	852100	8	8	7
03.01.2017	13:58	11.01.2017	09:17	353243	plukk	1	1	7
03.01.2017	14:22	05.01.2017	10:29	353244	629100	5	5	2
03.01.2017	14:22	26.01.2017	11:04	353244	531800	7	7	18
03.01.2017	14:22	05.01.2017	10:29	353244	533122	2	2	2
03.01.2017	14:22	05.01.2017	10:29	353244	619100	2	2	2
03.01.2017	14:22	05.01.2017	10:29	353244	839100	1	1	2
03.01.2017	14:22	05.01.2017	10:29	353244	331418	1	1	2
03.01.2017	14:22	26.01.2017	11:04	353244	533416	1	1	2

[Phil_Seamark]

Hi @UlliJ

This calculated column gives you the average duration per order.  Is this along the lines of what you need?

Average 1 = CALCULATE(AVERAGE('Table3'[Behandlingstid]),ALLEXCEPT('Table3','Table3'[Ordrenr]))