VALUES AND ALL FUNCTION AS ARGUMENT FOR AVERAGEX

DASILAR1 — Thu, 06 Apr 2023 13:47:04 GMT

Good morning.

I have a question to solve a DAX formula. After struggling for a long time, I believe I have identified the problem, but I have not yet been able to find a solution.

I created a Measure [TX_Visita]

I need to create a measure that calculates the average of [Tx_Visitas] for each [Service Type and Customer Code]. Below is an example.

I created the TempoMedioVisitas measure, which worked very well. It adds the [Tx_Visista] for each customer (2.33+0.77+0) and divides by the 3 customers.

However, when I analyzed it, I noticed that those customers who had only one visit (TxVisita=0) end up distorting the result. So I tried to create another measure that does the same calculation, but disregards those customers with TxVisita=0.

I created TempoMedioVisita2. But the value it returns is completely wrong. The idea was to add 2.33 and 0.77 and divide by 2, the result should be 1,55 not 1,06.

I have come to the conclusion that the error is most likely in the first argument of the AVERAGEX() function, where I need to pass a table as an argument.

I need to create a context where I have for each service type all the customers who have already performed it, but in a unique way. That is, if customer 3727 has already performed 5 Criolipolysis, I need the [3727 Criolipo] row to appear only once, but the ALL function returns all times. The VALUES() function, which does exactly what I need, does not accept two columns as arguments.

Can anyone help me? Is my reasoning correct, is the error really this, and if so, how can I fix it?

Tks.

Re: VALUES AND ALL FUNCTION AS ARGUMENT FOR AVERAGEX

MohammadLoran25 — Thu, 06 Apr 2023 14:23:46 GMT

Hi DASILAR1 ,

1-Create the measure:

ValueMeasure = SUM(Consulta[VALUE])

2-Create the desired measure:

AverageMeasureValue = CALCULATE ( AVERAGEX ( VALUES ( Consulta[CustomerCode] ), [ValueMeasure] ), FILTER ( Consulta, Consulta[VALUE] <> 0 && NOT ISBLANK ( Consulta[VALUE] <> 0 ) ) )

If this answer solves your problem, give it a thumbs up and mark it as an accepted solution so the others would find what they need easier.

Regards,
Loran

Re: VALUES AND ALL FUNCTION AS ARGUMENT FOR AVERAGEX

DASILAR1 — Thu, 06 Apr 2023 14:45:02 GMT

I managed to find the solution.
I'll leave it below in case anyone has a similar question.

topic Re: VALUES AND ALL FUNCTION AS ARGUMENT FOR AVERAGEX in DAX Commands and Tips

VALUES AND ALL FUNCTION AS ARGUMENT FOR AVERAGEX

Re: VALUES AND ALL FUNCTION AS ARGUMENT FOR AVERAGEX

Re: VALUES AND ALL FUNCTION AS ARGUMENT FOR AVERAGEX