cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
darko861
Helper II
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?
Your assistance is much appreciated.

 

darko861_1-1629908837592.png

 

 

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

 

darko861_0-1629918267147.png

 

View solution in original post

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

 

darko861_0-1629918267147.png

 

View solution in original post

nvprasad
Solution Specialist
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,
N V Durga Prasad

Hi,

this does not work, it gives an error

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.