Average/Sum of distinct count ?

[julienbr]

Hi everyone,

I have read a lot about what I think is related to my problem but I don't seem to be able to adapt the solutions I have found to my specific case.. 😞 Many thanks in advance to those who will try to help me ! 🙂

Here is a simplified view of the database I am working with :

DAYS	SHOP	GROUP	PRODUCT
DAY 1	SHOP 1	GROUP 1	PRODUCT 1
DAY 1	SHOP 1	GROUP 1	PRODUCT 2
DAY 1	SHOP 1	GROUP 2	PRODUCT 1
DAY 1	SHOP 1	GROUP 2	PRODUCT 2
DAY 1	SHOP 2	GROUP 1	PRODUCT 1
DAY 1	SHOP 2	GROUP 1	PRODUCT 2
DAY 1	SHOP 2	GROUP 2	PRODUCT 1
DAY 1	SHOP 2	GROUP 2	PRODUCT 2
DAY 1	SHOP 2	GROUP 2	PRODUCT 3
DAY 2	SHOP 1	GROUP 1	PRODUCT 1
DAY 2	SHOP 1	GROUP 1	PRODUCT 2
DAY 2	SHOP 1	GROUP 2	PRODUCT 1
DAY 2	SHOP 1	GROUP 2	PRODUCT 2
DAY 2	SHOP 2	GROUP 1	PRODUCT 1
DAY 2	SHOP 2	GROUP 1	PRODUCT 2
DAY 2	SHOP 2	GROUP 2	PRODUCT 1
DAY 2	SHOP 2	GROUP 2	PRODUCT 2
DAY 2	SHOP 3	GROUP 1	PRODUCT 1
DAY 2	SHOP 3	GROUP 1	PRODUCT 2
DAY 2	SHOP 3	GROUP 2	PRODUCT 1
DAY 2	SHOP 3	GROUP 2	PRODUCT 2

I am looking for a DAX measure giving me the following result :

(number of shops in DAY 1 + number of shops in DAY 2) / number of DAYS = (2 + 3) / 2 = 2,5.

I thought I had to use DISTINCTCOUNT and SUMX but I cannot make it work... 😞

Many thanks !

Julien

[Greg_Deckler]

How about this

MyMeasure = 
VAR tmpTable1 = SUMMARIZE('#Shops',[DAYS],"Shops",COUNTROWS(DISTINCT('#Shops'[SHOP])))
RETURN SUMX(tmpTable1,[Shops])/COUNTROWS(tmpTable1)