DISTINCTCOUNT in measure - Sum of subtotals issue

[depple]

Hi,

Background: I wish to create a matrix that uses a measure that distinctcounts the number of base products in each store. Each store has a responsible salesman.

Here are two mockup tables of my data:

Inventory table
Store ID	SKU ID	SKU	Base Product
1	1	Product A - Small Box	Product A
1	2	Product A - Big box	Product A
1	3	Product B - Small Box	Product B
1	4	Product B - Big box	Product B
1	5	Product C - Small Box	Product C
1	6	Product D - Big box	Product D
1	7	Product D - Small Box	Product D
2	1	Product A - Small Box	Product A
2	2	Product A - Big box	Product A
2	3	Product B - Small Box	Product B
2	4	Product B - Big box	Product B
2	5	Product C - Small Box	Product C

Store Data
Store ID	Store Name	Responsible
1	Store A	John
2	Store B	John

I have created a 'matrix' below where the correct hierarchy totals for this measure should be like according to the mockup tables:

Problem: I get it correct at Store Name level by using DISTINCTCOUNT, but I have ground to a halt with the total at Responsible level.

In order to adjust the counting method with the hierarchy level, I believe that ISINSCOPE could be of help:

My current measure:

Number of base products =

IF( ISINSCOPE('Store Data'[Store Name]),
DISTINCTCOUNT('Inventory Table'[Base Product]),

IF( ISINSCOPE('Store Data'[Responsible]),
[What to put here???]
)

I would really appreciate any help on this.

Regards,

depple

[amitchandak · Accepted solution]

@depple , Try a measure like

Sumx(Values('Store Data'[Store Name]),calculate(DISTINCTCOUNT('Inventory Table'[Base Product])))

[depple]

@amitchandak 

Wow, as far as I can observe, it works perfect!

Thank you!

Regards,

depple