Forum Discussion

damit23183's avatar
damit23183
Microsoft Employee
6 years ago

Matrix Table not working as expected

Hi,

 

I am working on to create Matrix table with given data where i am looking for count based on Two Categories and SUM of Total Rows and Columns at end.

 

This is what i have;

 

 

 

The result i am looking for is like;

As you can see in above screen shot, Sum of Total column and Rows based on Categories. 

 

But, I am getting unexpected result which you can see below;

 

So far the number inside the tables are correct but totals are incorrect my case. Please help me with solution becuase it does look like simple for many expert where i am really struggling to get correct total. Also, you can see total column are distinct count of case which correct but i want SUM of column not count of column. The same case with ROW total.

 

Let me know if you need any more information, i would be more than happy to provide you any time.

 

Thanks in advance.

Need Help

5 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    Is your measure a distinctcount?  If so, try just a regular COUNT().  Also, what columns are you putting on the matrix to get the A and C columns?  Basis is the parent column, but are A and C Age and Color?

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

    • damit23183's avatar
      damit23183
      Microsoft Employee
      6 years ago

      HI,

       

      THank you for your response.

       

      Yes i have already use COUNT() which is C in this case.

       

      Here C and A are two different value columns which are counting number of cases and number (C) of Aggrievedname (A). Each Basis will have C and A.

       

      Thanks

      • mahoneypat's avatar
        mahoneypat
        Microsoft Employee
        6 years ago

        I looked again at your initial post and both the data and pivot tables.  It really looks like it is working correctly.  It appears that maybe you have selected to do a distinctcount aggregation on the CaseID column, which would result in that table.  Which field did you place in the values area of your pivot table, and what is the selected aggregation?  I think you just want a straight count of the case ID column (since you have duplicate case ID values in that first column.

         

        If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

        Regards,

        Pat

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi damit23183 ,

     

    Sorry for if i missed some information but i don't quite understand what the "A" and "C" are in your visual?

     

    Best Regards,

    Jay