Averagex based on filter condition

[darko861]

Hi all,

I'm trying to calculate the average of a column (measure Rev_per_hour_1) from a visual table that has a filter applied on the column Rev_per_hour 3 (measure). Unfortunately, my measure ignores the filter which is applied on Rev_per_hour 3 and does not give me the desired result based on the Customers that are displayed in the table. I get 2,725.26 which is the average from all of the customers, but I only want to calculate the average of the visible customers in the table below, which should be 3,438.31

I have a second filter applied to the table as well, column Billed_Agr_Hours (from the Table PivotTable).

The measure that I have used for the average is 

Average Rev 1 = AVERAGEX(VALUES(PivotTable[D2021_Foeretag]),[xxx_rev_per_hour1])

How can I tweak the code above to only calculate the average of the visible customers in the table?

Your assistance is much appreciated.

[darko861 · Accepted solution]

Hi,

I managed to solve this issue by altering the measure that is in bold text:

Average Rev 1 = AVERAGEX(VALUES(PivotTable[D2021_Foeretag]),[xxx_rev_per_hour1])

The measure should look like this: 

xxx_rev_per_hour1 = IF([xxx_rev_per_hour3]=BLANK(),BLANK(),DIVIDE([xxxrev1],[xxxrev1.0],BLANK()))

And you will get the desired result.

[nvprasad]

Hi,

Could you please try with below dax.

=CALCUATE(AVERAGEX(VALUES(PivotTable[D2021_Foeretag]),[xxx_rev_per_hour1]),KEEPFILTERS(customernamecolumn))

Appreciate a Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂

Regards,
N V Durga Prasad

[darko861]

Hi,

this does not work, it gives an error

= CALCULATE(AVERAGEX(VALUES(PivotTable[D2021_Foeretag]),[xxx_rev_per_hour1]),KEEPFILTERS(PivotTable[D2021_Foeretag]))