Forum Discussion
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
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
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 PrasadHi,
this does not work, it gives an error
= CALCULATE(AVERAGEX(VALUES(PivotTable[D2021_Foeretag]),[xxx_rev_per_hour1]),KEEPFILTERS(PivotTable[D2021_Foeretag]))
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.
