Create Matrix counting rows from sliced data

Question

Hi.&nbsp; I have created a filter that looks at the table and identifies advisors with less than 10 sales.&nbsp; There is a slicer that breaks them down by Region, then by District.&nbsp; I created a Measure that counts the number of advisors that result: DISTINCTCOUNT(Advisors[Advisor Name]).&nbsp; I did this for 3 products.&nbsp; So far so good.&nbsp; The results look like this:&nbsp;Region&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; District&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Advisor Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RTAs&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; MeasureAtlantic&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Capitol&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; John Jones&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1Atlantic&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; District 2 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Jeff Johnson &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1Atlantic&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; District 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Jill Jackson &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; . &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Total&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 37&nbsp;Then I wanted to create a Matrix that would show rows for Region and District, and columns by product name, showing only the number of advisors in each district (or however the data was sliced) who had less than 10 sales of each product.&nbsp; The table looks like this (using Count(Distinct) for each of the fields ):&nbsp;Region&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Count of RTAs&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Count of Pathfinders&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Count of 360sAtlantic&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 17&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8&nbsp;&nbsp;&nbsp; Capitol District&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 17&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8Total&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 17&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8&nbsp;So the problem is that in this case, Count of RTAs should be 37, Count of Pathfinders should be 43, and count of 360s should be 47.&nbsp; I can't seem to get them to match up and I can't tell where it is getting the numbers being displayed.&nbsp;Is there a simpler way to do it?&nbsp;Thanks for any help!

v-qiuyu-msft · Answer

Hi tborg,
&nbsp;
In your matrix, did you place Region and District columns in Rows, Count of RTAs, Count of Pathfinders, Count of 360s in Values? I'm not very clear about "So the problem is that in this case, Count of RTAs should be 37, Count of Pathfinders should be 43, and count of 360s should be 47.&nbsp;&nbsp;"
&nbsp;
Would you please share some dummy data and tell&nbsp;the expected results in matrix?
&nbsp;
Best Regards,QiuyunYu

tborg · Answer

Yes, Region and District are in Rows, Count of RTAs, etc. are in Values, using Count (Distinct).

Here is some data:

Region	District	Total RTAs	Filtered RTAs
Atlantic	Capitol District	9	9
Atlantic	Capitol District	13
Atlantic	Capitol District	11
Atlantic	Capitol District	2	2
Atlantic	Capitol District	3	3
Atlantic	Capitol District	4	4
Atlantic	Capitol District	2	2
Atlantic	Capitol District	0	0
Atlantic	Capitol District	0	0
Atlantic	Capitol District	23
Atlantic	Capitol District	7	7
Atlantic	Capitol District	4	4
Atlantic	Capitol District	5	5
Atlantic	Capitol District	5	5
Atlantic	Capitol District	7	7
Atlantic	Capitol District	7	7
Atlantic	Capitol District	2	2
Atlantic	Capitol District	15
Atlantic	Capitol District	1	1
Atlantic	Capitol District	7	7
Atlantic	Capitol District	15
Atlantic	Capitol District	7	7
Atlantic	Capitol District	3	3
Atlantic	Capitol District	8	8
Atlantic	Capitol District	3	3
Atlantic	Capitol District	4	4
Atlantic	Capitol District	6	6
Atlantic	Capitol District	0	0
Atlantic	Capitol District	9	9
Atlantic	Capitol District	8	8
Atlantic	Capitol District	3	3
Atlantic	Capitol District	10
Atlantic	Capitol District	2	2
Atlantic	Capitol District	3	3
Atlantic	Capitol District	0	0
Atlantic	Capitol District	19
Atlantic	Capitol District	4	4
Atlantic	Capitol District	19
Atlantic	Capitol District	15
Atlantic	Capitol District	1	1
Atlantic	Capitol District	14
Atlantic	Capitol District	1	1
Atlantic	Capitol District	0	0
Atlantic	Capitol District	1	1
Atlantic	Capitol District	1	1
Atlantic	Capitol District	8	8
Atlantic	Capitol District	0	0
		47	37

Whaat's missing in the above data is the Advisor Name, which was necessary in order to give me the distinct values to sort. "Filtered RTAs" is actually the measure I used, which results in "1" for each line, which when summed is 37.

This is what the matrix should look like, but the "Count (Distinct)", which is counting the distinct number of Advisor Names, returns 17 rather than 37.

Region	Count of RTAs
Atlantic	37
Capitol District	37
Total	37

I get similar results from the other 2 products as well, so if I can figure out the problem here, I can apply that to the others as well.

v-qiuyu-msft · Answer

Hi tborg,
&nbsp;
From&nbsp;the data you provided, the Count of Filtered RTAs is 37. But how Count of RTAs be calculated, as there should be a column contains RTAs values.
&nbsp;
If your report doesn't contain sensitive data, would you please share pbix file here? As the sample data you provided seems not raw data but aggregated results from a visual. Please share raw data from Data tab like below which used in the matrix visual.
&nbsp;

&nbsp;
Best Regards,QiuyunYu

Forum Discussion

Create Matrix counting rows from sliced data

3 Replies

Related content

How to create drill-down matrix hierarchy with pre-aggregated data without double-counting?

Counting nulls

Create count measure

Matrix table

Creating matrix