Average by region per account

[Tevon713]

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.

[ghoshabhijeet · Accepted solution]

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

[Tevon713]

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

[ghoshabhijeet]

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

[Tevon713]

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?

[ghoshabhijeet]

@Tevon713  Please find my responses below:

1. Yes

2. Yes

[ghoshabhijeet]

@Tevon713  Can you please provide the sample data ?

[Tevon713]

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