COUNTIF in PowerPivot

PierreZ

New Member
Joined
Sep 20, 2013
Messages
3
I'm trying to accomplish the same as with a normal COUNTIF function in Excel but in PowerPivot. The function is =COUNTIF(A:A,A1).

The result should look like this:

ItemsCount_Distinct_Item
B180030.989.L2
B180030.989.M1
B180030.989.XL2
B180030.001.L1
B180030.001.M2
B180030.001.S1
B180030.001.M2
B180030.989.L2
B180030.989.XL2
B180030.989.XXL1

<colgroup><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
This sounds like a job for =CALCULATE(COUNTROWS(DISTINCT([Column])), conditions of the IF you desire<filters for="" if="" conditions=""><filters>)



I'm trying to accomplish the same as with a normal COUNTIF function in Excel but in PowerPivot. The function is =COUNTIF(A:A,A1).

The result should look like this:

ItemsCount_Distinct_Item
B180030.989.L2
B180030.989.M1
B180030.989.XL2
B180030.001.L1
B180030.001.M2
B180030.001.S1
B180030.001.M2
B180030.989.L2
B180030.989.XL2
B180030.989.XXL1

<tbody>
</tbody>
</filters></filters>
 
Upvote 0
Hi Butlerrbrian

Thanks for the reply, however, what would be the if condition for counting the amount of unique values in the Items column? I cant seem to get it right. It keeps giving me an error.

My formula looks like this: =CALCULATE(COUNTROWS([ITEMS]),[ITEMS]=[ITEMS])
 
Upvote 0
COUNTROWS() only accepts tables as an arguement. To count a column you must use either COUNT() or COUNTA().

That being said, this should give you the result you are looking for:

Code:
=CALCULATE(COUNTROWS(TableName), ALLEXCEPT(TableName, TableName[Items]))
 
Upvote 0
COUNTROWS() only accepts tables as an arguement. To count a column you must use either COUNT() or COUNTA().

That being said, this should give you the result you are looking for:

Code:
=CALCULATE(COUNTROWS(TableName), ALLEXCEPT(TableName, TableName[Items]))

Hi there

does this only work on a one column powerpivot database?

I'm trying to accomplish the same result, I have a small database that's pulled in from Access. It contains data on patients, their hospital attendances, which hospital, etc. What I would like to do is add columns where I can calculate:
a) the count of attendances by that patient ID (so just one condition like the countif(A:A,A1) example above)
b) count of attendance by that patient in a given year
c) count of attendance by a patient in that year at a particular hospital.

I've tried the allexcept example given, where I selected all columns in the table except the patientID and it didn't work.

Any help would be much appreciated.

Mejd
 
Upvote 0
Hi there

does this only work on a one column powerpivot database?

I'm trying to accomplish the same result, I have a small database that's pulled in from Access. It contains data on patients, their hospital attendances, which hospital, etc. What I would like to do is add columns where I can calculate:
a) the count of attendances by that patient ID (so just one condition like the countif(A:A,A1) example above)
b) count of attendance by that patient in a given year
c) count of attendance by a patient in that year at a particular hospital.

I've tried the allexcept example given, where I selected all columns in the table except the patientID and it didn't work.

Any help would be much appreciated.

Mejd


For those who googled the countif problem in PowerPivot and ended in this thread: The provided formula should work if you replace all comma's ( , ) with a semicolon ( ; ).
 
Upvote 0
Hi everyone!

I have encountered the exactly same issue except that I have no powerpivot option in my add-in...
I'm essentially trying to achieve the same result as the OP did, would anyone be able to advise on how I can do this in normal pivot table? Thanks, will be much appreciated!!!
 
Upvote 0
Hi everyone!

I have encountered the exactly same issue except that I have no powerpivot option in my add-in...
I'm essentially trying to achieve the same result as the OP did, would anyone be able to advise on how I can do this in normal pivot table? Thanks, will be much appreciated!!!

Just use the =COUNTIF on the dataset before you Pivot it
 
Upvote 0

Forum statistics

Threads
1,213,585
Messages
6,114,513
Members
448,575
Latest member
hycrow

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top