AVERAGE IF at least one line contains ...

[Jo_Chrq]

Hi all,

I'm struggling to find the right way to do the following calculation:

I have a Sales table which contains ticket numbers. Each ticket has several lines for each item sold.

Items have categories.

I would like to find the aveage quantity sold for tickets that have sold at least one item with category A.

Ticket	Article category	Article	Quantity	Amount (€)
111	A	Article xxx	1	100 €
111	B	Article xxx	2	10 €
111	C	Article xxx	1	20 €
112	B	Article xxx	1	15 €
112	D	Article xxx	1	12 €
113	B	Article xxx	1	5 €
113	A	Article xxx	1	150 €
113	C	Article xxx	1	23 €
113	D	Article xxx	2	24 €
113	F	Article xxx	1	15 €
114	E	Article xxx	3	19 €
114	B	Article xxx	1	10 €
114	B	Article xxx	2	9 €
115	A	Article xxx	1	120 €
115	E	Article xxx	1	2 €
115	F	Article xxx	3	15 €

In this example, it would be :

Average = (ticket 111 = 4) + (ticket 113 = 6) + (ticket 115 = 5) / 3 (tickets) = 15 / 3 = 5

May thanks in advance for your help.

Best

Jonathan

[Jo_Chrq]

Hi @AntonioHR_ 

I don't if it's the best way but it's definitely a way!

It works just fine, thansk for your help.

Best

[AntonioHR_ · Accepted solution]

Hello,
I'm sure this is not the best solution but will work.

Create a table only with tickets with "A" category:
table2 =

CALCULATETABLE(DISTINCT(Table[Ticket]); Table[Article category] = "A")
Link it with your original table by ticket and create a new column in the original table:
Column = RELATED(Table2[ticket])
Then you can do the measure to calculate the average:

MEASURE = CALCULATE(
SUM(
Table[Quantity])/DISTINCTCOUNT('Table2'[Ticket]);
Table[Column] <> BLANK()

)