cancel
Showing results for
Did you mean:
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.

1 ACCEPTED SOLUTION
Super User

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

Without applying filter:

With applied filter:

 ** 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 👍

7 REPLIES 7
Super User

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

Without applying filter:

With applied filter:

 ** 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 👍

Super User

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

Super User

@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 👍

Super User

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?

Super User

@Tevon713  Please find my responses below:

1. Yes

2. Yes

Super User

@Tevon713  Can you please provide the sample data ?

Super User

Thanks @ghoshabhijeet. Please see below.

 Year Date Region Site Name Operating Days Office Counts Transaction Amt Total Sales 2021 02/04/21 West Site 1 253 11689 2503 15557 2021 08/23/21 West Site 2 253 3344 10 3675 2021 01/05/21 West Site 3 253 1577 1620 3725 2021 08/15/21 South Site 55 253 31531 286 2901 2021 11/23/21 South Site 66 253 37213 4979 46088 2019 05/18/19 South Site 4 253 19372 2633 24495 2021 06/08/21 North Site 2 253 0 0 0 2020 12/16/20 North Site 9 253 180 437 618 2019 07/09/19 North Site 8 253 0 0 0 2019 04/12/19 West Site 1 253 63 0 70 2020 10/07/20 West Site 2 253 157 0 259 2021 12/07/21 West Site 3 253 0 0 0

Announcements