DAX: Average over data gaps

[Anonymous]

Hi!

I'm creating a measure for average machine capacities and wonder if there is an easy way to deal with gaps in data. Let's say there are 3 available machines (A, B, C) and that I have these facts:

Machine, date, capacity

A, 2016-01-10, 10 hours

A, 2016-01-12, 30 hours

A, 2016-01-14, 50 hours

B, 2016-01-10, 10 hours

B, 2016-01-16, 20 hours

Let's say I want to see average daily capacity per machine in january, so I take AVERAGE(capacity). Then I get 24 because (10+30+50+10+20)/5 = 24. But that's of course not correct since the data set only includes registered records. What I do want is the denominator to include all days (31) and machines (3). So instead I can do SUM(capacity) / ( DISTINCTCOUNT(DateDimension[date]) * DISTINCTCOUNT(MachineDimension[machine]) ). This gives me (10+30+50+10+20)/(31*3) = 1.29 which is what I want. 

BUT what I don't like about such solution is that it's not dynamic. Let's say I add another dimension Country to my model. Then I'd have to remember to update my measure to also include Country in the denominator. Isn't there an easier way to do this? Something with a CROSS JOIN could also solve it but also clumsy I guess!? I can't understand that this isn't a more common problem for people. It basically occurs everytime you want an average of some facts with gaps.

[Eric_Zhang · Accepted solution]

@Anonymous

I  think it all depends on the data model in your case. Check a simple demo below.