Forum Discussion

lardo5150's avatar
lardo5150
Microsoft Employee
6 years ago
Solved

Count value in a column based on another column

I have imported a SharePoint List as my data. Right now, I have something like this:     I want to be able to sort this by engineer, and count how many As they had, how many Bs they had, and ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi lardo5150 ,

     

    Please check the following steps.

    1# Create a Table like below:

    2# Create a measure like below:

    Measure = count('Table'[type])+0

    3# Create a matrix visual as below:

     

    Best Regards,

    Jay

    Community Support Team _ Jay Wang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi lardo5150 ,

 

Please check the following steps.

1# Create a Table like below:

2# Create a measure like below:

Measure = count('Table'[type])+0

3# Create a matrix visual as below:

 

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • lardo5150's avatar
    lardo5150
    Microsoft Employee
    6 years ago

    so when I do that, I get weird data.

     

    I create a new column in Data

     

     

    Measure = COUNT('Acceptance History'[CRITSIT])+0

     

    I setup my matrix

     

     

    Now what is happening, it is going through the CRITSIT column and adding up all cells with a value of CRITSIT, Normal, Aged, etc... and applying that same total to each engineer, instead of showing me how many each engineer had.

    So in this data, there was 13815 Critsits, TOTAL, in my data set.  It is giving each engineer that same total, instead of showing that engineer 1, for example, had 3.

     

    The other thing I am looking for is once this is fixed, how to break out each total, for each engineer, into its own card.

     

     

    • lardo5150's avatar
      lardo5150
      Microsoft Employee
      6 years ago

      If I use this:

       

      Total Cases = CALCULATE(COUNTROWS('Acceptance History'),ALLSELECTED('Acceptance History'[Engineer]),VALUES('Acceptance History'[CRITSIT]))

       

      That works, but I can't create seperate columns for the total sum of each value in the CRITSIT column when using the above.

       

      I am trying to create additional counts or measures, that will give me the total of the value for each engineer, so I can put that into a card.

      • lardo5150's avatar
        lardo5150
        Microsoft Employee
        6 years ago

        Ok, I figured out how to add a measure, I see what you were doing.

        I ahve that working.

         

        Any idea, how I get the measure, for each value based on engineer, so I can put that total in the Card?

         

        So, using the first measure, for example, shows me engineer 1 got 3 Critsits, 2 normal.  I want a card that shows the total Critsits.