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.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

Matrix visual, put Engineer in the rows, put Type in the Columns. Put Date in the Values and use Count.

lardo5150's avatar
lardo5150
Microsoft Employee
6 years ago

I need to add that I am learning as I go, today was the first time using PowerBI, so I am BRAND NEW.

 

I was using this to get the total...

 

Total Cases = CALCULATE(COUNTROWS('mydataset'),ALLSELECTED('mydataset'[Engineer]),VALUES('mydataset'[Type]))

 

So this was giving me the total.  I am using a normal table, am I not able to use a normal table?

  • lardo5150's avatar
    lardo5150
    Microsoft Employee
    6 years ago

    sorry, forgot to add, I am using a slider for the date.

     

    • lardo5150's avatar
      lardo5150
      Microsoft Employee
      6 years ago

      I see what you are saying now with the Matrix.

      Where and how do I enter in the Count?

      • lardo5150's avatar
        lardo5150
        Microsoft Employee
        6 years ago

        So here is what I am running into.

         

        I have the total working.

        When I go into my dataset, I have created three extra columns.

        Total Cases

        Normal

        CRITS

         

        I have created this in the TOTAL CASES Column

        Total Cases = CALCULATE(COUNTROWS('Acceptance History'),ALLSELECTED('Acceptance History'[Engineer]),VALUES('Acceptance History'[CRITSIT]))
         
        This allows me to show each engineer, and how many Normal Cases they took, How many Critsits they took, How many hot cases they took, etc..  These are all values under the 'Acceptance History'[CRITSIT] column.
         
        But I am unable to break it out from there.  My report will break out the totals of each.  But I am trying to create a card for each of these values.  A card for Critsit, Normal, Hot Case, etc....and show the total in each.
         
        Everytime I try to set this up, I get an error with the most common a circular dependency.