topic Re: Average of a count entries without group by in DAX Commands and Tips

Average of a count entries without group by

Anonymous — Wed, 28 Oct 2020 15:58:58 GMT

Hello. I have a some columns in my file that correspond to customer's orders: order date, payment date, Number of order, product typology, etc. The behavior of the file is: when some customer order more than 1 item, it creates two lines for the same order, for example:

Num order	Orderdate	Sku	article typology	Count of Num articles	Mono or multi orders
123456	10/12/2019	65433	532	2	Multi
123456	10/12/2019	65990	597	2	Multi
23456	10/12/2019	65990	597	1	Mono

The 2 last columns were calculations done by me: if a order number appears more than 1 time, it is an order that have more than 1 article.

Now I want to know the average of article's per multi orders but I don't want to do a group by (by Number of order) to perform this. The way I know is to perform a query that groups by Num of order (all lines of the same order are consolidated in only one) and then do the average.

How to do?

I tried "average_num_articles:=CALCULATE(AVERAGE(Table[Count of Num articles]);FILTER(Table;VALUES(Table[NumOrder])))" without success.

Thank you.

Re: Average of a count entries without group by

AntrikshSharma — Wed, 28 Oct 2020 16:25:21 GMT

Anonymous Let me know if I wasn't able to understand your request:

Average of Articles = VAR CurrentOrder = jmsm[Num order] VAR MultiOrders = FILTER ( jmsm, jmsm[Mono or Multi Orders] = "Multi" && jmsm[Num order] = CurrentOrder ) VAR Result = AVERAGEX ( MultiOrders, jmsm[article typology] ) RETURN Result

PBI file attached below my signature:

Re: Average of a count entries without group by

Anonymous — Wed, 28 Oct 2020 16:50:36 GMT

AntrikshSharma The average is on "count of num of articles", not articles typology.

It didn't work when I tried in a cell (dax formula): The OrderNum cannot be determinated. "This happens when a measure formula refers to a columns that contains many values ..."

I think is related to the first VAR (currentorder). When I tried to write Table[NumOrder] it didn't recognize it (only columns with calculations).

Re: Average of a count entries without group by

AntrikshSharma — Wed, 28 Oct 2020 16:57:37 GMT

Anonymous Is this what you want?

Re: Average of a count entries without group by

Anonymous — Wed, 28 Oct 2020 17:02:21 GMT

Yes, but in a measure not in a column because the average is by OrderNumber and not by each line of each order number.

Re: Average of a count entries without group by

AntrikshSharma — Wed, 28 Oct 2020 17:08:10 GMT

Anonymous Most of the code is same for Measure:

Measure = VAR CurrentOrder = SELECTEDVALUE ( jmsm[Num order] ) VAR MultiOrders = FILTER ( ALL ( jmsm ), jmsm[Mono or Multi Orders] = "Multi" && jmsm[Num order] = CurrentOrder ) VAR Result = AVERAGEX ( MultiOrders, jmsm[Count of num of articles] ) RETURN Result