topic AVERAGEX - calculated vertically instead of horizontal in DAX Commands and Tips

AVERAGEX - calculated vertically instead of horizontal

jayjay0306 — Tue, 13 Aug 2024 14:05:20 GMT

hello Experts,

I hope you can help me with a challange:

Very "boiled down", I have four tables in Power BI, "customer" (columns: customer, customer chain), "Activity" (column: "number of contacts made to the customer"), "Organisation" (column: "Sales person").

I have a "matrix"-visual showing the average number of contacts per customer on "Chain"-level for each Sales person:

now, If I want to calculate the average of chains for each sales person, I have made this measure:

Average contact per Chain= VAR ChainAverage = AVERAGEX( 'Activity', 'activity'[index] ) RETURN AVERAGEX( VALUES('Customer'[Customer Chain]), ChainAverage )

which, in essense, works.

However, my problem is that the "Average total" is not correct (if I manually calculate the average shown, I get the averages shown in red). I expect the reason for this is that power bi calculates on the row in context. I.e. it calculates the "Average total" horizontal. What I need, is to "force" the measure to calculate the average vertically.

Do anyone know how to do this?

thanks. All ideas are much appreciated.

Br,

Jayjay0306

Re: AVERAGEX - calculated vertically instead of horizontal

AlexisOlson — Tue, 13 Aug 2024 16:05:37 GMT

Your second AVERAGEX isn't doing anything since ChainAverage is a constant value once it's defined (it's the same for each row in the customer chain iterator).

I think you might want something more like this:

AVERAGEX ( SUMMARIZE ( Activity, Customer[Customer Chain], "@ChainAvg", AVERAGE ( activity[index] ) ), [@ChainAvg] )

(This assumes you have a one-to-many relationship from Customer to Activity.)

Re: AVERAGEX - calculated vertically instead of horizontal

Anonymous — Wed, 14 Aug 2024 05:31:39 GMT

Hi jayjay0306 ,

I create a table as you mentioned.

Then I put it into the matrix visual.

If you want the vertical average column you are talking about, I think you can just make the change.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: AVERAGEX - calculated vertically instead of horizontal

jayjay0306 — Wed, 14 Aug 2024 07:21:29 GMT

Thanks AlexisOlson, it is exactly what I want.

bloody brilliant 🙂

br,

Jayjay0306