How to count the average number of facts per day in PowerPivot DAX

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.

  • Surush

    Great, as I was looking for:) You the man.