Count value in a column based on another column

[lardo5150]

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 if they had zero, it will put 0 into the next column, like this:

I THINK I have the TOTAL number of cases set as when I try to add up all the As, it gives me the total number (which is As and Bs) for each engineer.  That is cool, and I want that, but I also want a count, per engineer, of how many As they have, and how many Bs they have.  When I try to modify the total formula I have, it just shows a blank cell.

What is the correct formulat to get Total As and Bs, Total As, and Total Bs, for each engineer.

[v-jayw-msft · Accepted solution]

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]

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]

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]

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.

[Greg_Deckler]

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

[lardo5150]

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]

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

[lardo5150]

I see what you are saying now with the Matrix.

Where and how do I enter in the Count?

[lardo5150]

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.