Forum Discussion

tborg's avatar
tborg
Helper I
9 years ago

Create Matrix counting rows from sliced data

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 c...
Need Help
v-qiuyu-msft's avatar
v-qiuyu-msft
Community Support
9 years ago

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's avatar
    tborg
    Helper I
    9 years ago

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

     

    Here is some data:

     

    RegionDistrictTotal RTAsFiltered RTAs
    AtlanticCapitol District99
    AtlanticCapitol District13 
    AtlanticCapitol District11 
    AtlanticCapitol District22
    AtlanticCapitol District33
    AtlanticCapitol District44
    AtlanticCapitol District22
    AtlanticCapitol District00
    AtlanticCapitol District00
    AtlanticCapitol District23 
    AtlanticCapitol District77
    AtlanticCapitol District44
    AtlanticCapitol District55
    AtlanticCapitol District55
    AtlanticCapitol District77
    AtlanticCapitol District77
    AtlanticCapitol District22
    AtlanticCapitol District15 
    AtlanticCapitol District11
    AtlanticCapitol District77
    AtlanticCapitol District15 
    AtlanticCapitol District77
    AtlanticCapitol District33
    AtlanticCapitol District88
    AtlanticCapitol District33
    AtlanticCapitol District44
    AtlanticCapitol District66
    AtlanticCapitol District00
    AtlanticCapitol District99
    AtlanticCapitol District88
    AtlanticCapitol District33
    AtlanticCapitol District10 
    AtlanticCapitol District22
    AtlanticCapitol District33
    AtlanticCapitol District00
    AtlanticCapitol District19 
    AtlanticCapitol District44
    AtlanticCapitol District19 
    AtlanticCapitol District15 
    AtlanticCapitol District11
    AtlanticCapitol District14 
    AtlanticCapitol District11
    AtlanticCapitol District00
    AtlanticCapitol District11
    AtlanticCapitol District11
    AtlanticCapitol District88
    AtlanticCapitol District00
      4737

     

    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.

     

    RegionCount of RTAs
    Atlantic37
       Capitol District37
    Total37

     

    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's avatar
      v-qiuyu-msft
      Community Support
      9 years ago

      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