cancel
Showing results for
Did you mean:  Helper II

## Averagex based on filter condition

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? 1 ACCEPTED SOLUTION  Helper II

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. 3 REPLIES 3  Helper II

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.   Solution Specialist

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,  Helper II

Hi,

this does not work, it gives an error

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