Create Matrix counting rows from sliced data

[tborg]

Hi.  I have created a filter that looks at the table and identifies advisors with less than 10 sales.  There is a slicer that breaks them down by Region, then by District.  I created a Measure that counts the number of advisors that result: DISTINCTCOUNT(Advisors[Advisor Name]).  I did this for 3 products.  So far so good.  The results look like this:

Region        District           Advisor Name          RTAs            Measure

Atlantic       Capitol           John Jones                 3                   1

Atlantic       District 2        Jeff Johnson               2                   1

Atlantic       District 3        Jill Jackson                  1                   1

.                     .                         .                          .                    .      

Total                                                                                        37

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.  The table looks like this (using Count(Distinct) for each of the fields 😞

Region                          Count of RTAs        Count of Pathfinders         Count of 360s

Atlantic                                    17                           14                                 8

    Capitol District                     17                           14                                 8

Total                                        17                           14                                 8

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.  I can't seem to get them to match up and I can't tell where it is getting the numbers being displayed.

Is there a simpler way to do it?

Thanks for any help!

[v-qiuyu-msft]

Hi @tborg,

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.  "

Would you please share some dummy data and tell the expected results in matrix?

Best Regards,
QiuyunYu

[tborg]

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]

Hi @tborg,

From 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.

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.

Best Regards,
QiuyunYu