A pretty simple question in PowerPivot would be: give me the average number of facts for each day data
Sounds easy right? All we have to do is determine the number of facts for each day that has data and do an average of that. The problem is .. how do you do that ?
So we have two tables, FactInventory and DimDate. We need to get the number of rows the fact table for each day we have data. I start by creating a calculated column in the date table to determine the number of rows per day.
=COUNTROWS(RELATEDTABLE(FactInventory))
So this gives us the number of facts for each day. We now can do an average on this number using the average in the PowerPivot Field list:
Sounds easy.. The trick here is that we use the calculated column to determine the number of facts for each day and use an average on that.
Great, as I was looking for:) You the man.