Sometimes calculating averages per day in your Power BI reports isn’t actually enough.
You might run into a scenario where you only actually sell during weekdays. You might be a retailer or a restaurant where you only sell in a specific period of days.
Now, if you go and run an averaging pattern in Power BI and you utilize the date table, it may actually run through every single day and your average might be lower because you will have days which will have zero results.
In this example, I’m going to show you how you can solve that issue by going through a scenario where we only sell during the working week. So we’re going to calculate average sales but only on weekdays.
All you have to do is change the shape or size of the table that we run some iterating logic through. We need to create a date table which doesn’t have the weekends in it and the trick is we need to do this virtually.
By utilizing the FILTER function, we can actually reduce the size of the date table virtually and then run this iterating logic through it via an AVERAGEX formula.
There is plenty to learn with this example. The general technique of how you can use virtual tables inside of iterating functions is a really, really important concept to understand in Power BI.
It’s a great concept to learn, iterating formulas with virtual tables inside, because there’s many applications where this would be relevant in your own analysis that you’re doing.
Once you understand how iterating functions work, it opens up a world of analytical potential and your formulas become that much more simplified, as do your data models.