I'm trying to calculate a business-logic in DAX which has turned out to be quite resource-heavy and complex. I have a very large PowerPivot model (call it "sales") with numerous dimensions and measures. A simplified view of the sales model:

+-------+--------+---------+------+---------+-------+

| State | City | Store | Week | Product | Sales |

+-------+--------+---------+------+---------+-------+

| NY | NYC | Charlie | 1 | A | $5 |

| MA | Boston | Bravo | 2 | B | $10 |

| - | D.C. | Delta | 1 | A | $20 |

+-------+--------+---------+------+---------+-------+

Essentially what I'm trying to do is calculate a DISTINCTCOUNT of product by store and week:

SUMMARIZE(Sales,[Store],[Week],"Distinct Products",DISTINCTCOUNT([Product]))

+---------+------+-------------------+

| Store | Week | Distinct Products |

+---------+------+-------------------+

| Charlie | 1 | 15 |

| Charlie | 2 | 7 |

| Charlie | 3 | 12 |

| Bravo | 1 | 20 |

| Bravo | 2 | 14 |

| Bravo | 3 | 22 |

+---------+------+-------------------+

I then want to calculate the AVERAGE of these Distinct Products at the store level. The way I approached this was by taking the previous calculation, and running a SUMX on top of it and dividing it by distinct weeks:

SUMX(

SUMMARIZE(Sales,[Store],[Week],"Distinct Products",DISTINCTCOUNT([Product]))

,[Distinct Products]

) / DISTINCTCOUNT([Week])

+---------+------------------+

| Store | Average Products |

+---------+------------------+

| Charlie | 11.3 |

| Bravo | 18.7 |

+---------+------------------+

I stored this calculation in a measure and it worked well when the dataset was smaller. But now the dataset is so huge that when I try to use the measure, it hangs until I have to cancel the process.

Is there a more efficient way to do this?