AVERAGEX - calculated vertically instead of horizontal

[jayjay0306]

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

[jayjay0306]

Thanks AlexisOlson, it is exactly what I want.

bloody brilliant 🙂

br,

Jayjay0306

[Anonymous]

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.

[AlexisOlson · Accepted solution]

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.)