Forum Discussion
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 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.
Hi lardo5150 ,
Please check the following steps.
1# Create a Table like below:
2# Create a measure like below:
Measure = count('Table'[type])+03# 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.
9 Replies
Matrix visual, put Engineer in the rows, put Type in the Columns. Put Date in the Values and use Count.
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?
sorry, forgot to add, I am using a slider for the date.
Hi lardo5150 ,
Please check the following steps.
1# Create a Table like below:
2# Create a measure like below:
Measure = count('Table'[type])+03# 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.
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.
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.
