Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
@depple , Try a measure like
Sumx(Values('Store Data'[Store Name]),calculate(DISTINCTCOUNT('Inventory Table'[Base Product])))
@depple , Try a measure like
Sumx(Values('Store Data'[Store Name]),calculate(DISTINCTCOUNT('Inventory Table'[Base Product])))
User | Count |
---|---|
50 | |
24 | |
18 | |
17 | |
14 |
User | Count |
---|---|
91 | |
86 | |
39 | |
25 | |
21 |