Simple CountIF function in PowerPivot -- please help

[thomsup]

Hi,

I'm working in Powerpivot and trying to make the simplest of Count functions and cannot figure it out. I keep getting an error message. Essentially, I have three columns... one that says an airline name, one that says the type of plane it ordered (Embraer or Bombardier), and the last column that says the year it ordered that plane.
So for instance, it might read:
(This image is in excel, but I am working in powerpivot..)

I need to count simply for each year from 2010 to 2015, how many Embraers were ordered, and how many Bombardiers were ordered, for each year.

So for instance, the result should be. Embraer for 2010 should be 2. Bombardier for 2010 should be 0. Embraer for 2011 should be 0. Bombardier for 2011 should be 1.

How on earth do I do this? I've tried just counting how many planes in general were ordered per year and tried doing a CALCULATE(COUNTROWS('table', column c=2010)) but this didn't work. I know my logic is wrong, but I have no idea why or how- I've read countless posts and they all deal with much more confusing statements than this. Please help! Thank you!!

Thomsup

 

[AlexandraT]

Not having the data in your powerpivot I might not understand exactly what you did. However, you shouldn't need a formula for a count, Just add "Airplane" to the Values label and use Airplane for Columns and Year for Rows (or viceversa, doesn't matter). If you do need to to use a formula, try
=CALCULATE(COUNTROWS(table),table[Airplane]="Bombardier") to count Bombardier planes
and
=CALCULATE(COUNTROWS(table),table[Airplane]="Embraer") to count Embraer planes
and use the Year for the Rows label.

Alex

 

[thomsup]

Not having the data in your powerpivot I might not understand exactly what you did. However, you shouldn't need a formula for a count, Just add "Airplane" to the Values label and use Airplane for Columns and Year for Rows (or viceversa, doesn't matter). If you do need to to use a formula, try
=CALCULATE(COUNTROWS(table),table[Airplane]="Bombardier") to count Bombardier planes
and
=CALCULATE(COUNTROWS(table),table[Airplane]="Embraer") to count Embraer planes
and use the Year for the Rows label.

Alex

Hi Alex,

Thank you so much for trying to help me. I don't understand what you mean by the Values label? I have to count not just the Bombardier planes but have to count them each time for each year. So for 2010, I need to have a Bombardier Count. For 2011, I need another Bombardier count for this year. For 2012, I need another Bombardier Count. Etc. etc.

Does this make any sense at all?

Thank you so much again,
Colleen