Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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 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 👍 |
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?
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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |