cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tevon713
Super User
Super User

Average by region per account

Hi.

 

I have simple table with date, region, site, and various account amounts as follow. Seeking to find average per region for nonblank account amount and average overall cumulatively.

 

Will have slicer filter for user selection on time period, region and site name. How can I achieve in finding for both average measure with slicer selection?

 

In excel simple formula, IFERROR((AVERAGEIF(range,"<>0")),0). I want to build this in power bi not through excel model.

 

Thank you all.

 

 

Tevon713_0-1652903285265.png

 

 

1 ACCEPTED SOLUTION
ghoshabhijeet
Super User
Super User

@Tevon713  Here is my solution. Try this if it works for you.

Without applying filter:

ghoshabhijeet_0-1652975036632.png

With applied filter:

ghoshabhijeet_1-1652975069507.png

 

** If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution, so that it comes in top of the search and help others. Thank you !

Good Luck 👍

 

View solution in original post

7 REPLIES 7
ghoshabhijeet
Super User
Super User

@Tevon713  Here is my solution. Try this if it works for you.

Without applying filter:

ghoshabhijeet_0-1652975036632.png

With applied filter:

ghoshabhijeet_1-1652975069507.png

 

** If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution, so that it comes in top of the search and help others. Thank you !

Good Luck 👍

 

Thank you @ghoshabhijeet . It work.  Whatif there an addtional filter ie Date filter? Will that skrew the formula?

@Tevon713  No, it will work fine.

 

** If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution, so that it comes in top of the search and help others. Thank you !

Good Luck 👍

 

Thanks. If I wish to find average for all other accounts in table, then will need to revise the formula and create each avg measure per account?

 

Secondly, whatif want to display user selection of more than one region  avg in a visual ie graph. Is that possible?

@Tevon713  Please find my responses below:

 

1. Yes

2. Yes

ghoshabhijeet
Super User
Super User

@Tevon713  Can you please provide the sample data ?

Thanks @ghoshabhijeet. Please see below.

 

YearDateRegionSite NameOperating DaysOffice CountsTransaction AmtTotal Sales
202102/04/21WestSite 125311689250315557
202108/23/21WestSite 22533344103675
202101/05/21WestSite 3253157716203725
202108/15/21SouthSite 55253315312862901
202111/23/21SouthSite 6625337213497946088
201905/18/19SouthSite 425319372263324495
202106/08/21NorthSite 2253000
202012/16/20NorthSite 9253180437618

2019

07/09/19NorthSite 8253000
201904/12/19WestSite 125363070
202010/07/20WestSite 22531570259
202112/07/21WestSite 3253000

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors