Need to Group Categories in a dimension

[domtrump]

I am stumped goggling this one. It sounds so simple (and it used to be with a standard pivot table), but I cannot figure out how to group items in a dimension so that I can add them together as one unit.

Let's say I have a column called "Pets". The possible values for Pets are: Collie, Great Dane, Poodle, Tabby, Siamese, Persian. In my Pivot Table, I want to see "Dogs" (the sum of Collie, Great Dane, Poodle) and Cats (the sum of Tabby, Siamese, Persian). I used to just create a calculated ITEM that added each category together. I know that is no longer available, so could anyone tell me the DAX-world equivalent to do this? I don't think it is a measure. If it is a calculated column, how do I go about adding it?

Thanks.

[Excel 2010]

 

[scottsen]

In our hypothetical table, do we already have Breed and Species columns? If so, assuming you had a measure, say... [Total Animal] := COUNTROWS(Pets) ... it would "just" work when you put Breed on rows... or Specicies on rows... or Species AND breed on rows...

 

[domtrump]

No, they are not columns. Just Pets is the column. Within the Pets column, I'd like to GROUP the Species into Cats and Dogs.
I guess I'm trying to do the equivalent of creating a calculated ITEM that was Dogs = Collie+ Great Dane+ Poodle.
In SQL, I'd just create a new column with CASE when PETS IN (Collie, Great Dane, Poodle) THEN 'Dogs' END.
But let's assume I do not have access to change the source data and must do this using Excel and PowerPivot.

 

[Kazlik]

Are you only grouping into two categories Cats or Dogs or are they other groups?

 

[domtrump]

Lets say just two

 

[Oeldere]

use a helpcolumn to determine if it is a cat or a dog (in your Original data).

You can use a table and VLookup for that kind of work.

 

[scottsen]

Your only real choice is to add a calculated column. =IF ([Breed] = "Collie" || [Breed] = "Great Dane" || [Breed] = "Pug", "Dog", "Cat")

 

[TimRodman]

I agree with Scott. One additional thing, you might try SWITCH if you find yourself needing a bunch of nested IFs. SWITCH accomplishes the same thing, but is easier to read in many cases. You can add as many conditions as you want, with the last argument being a catch-all like a final ELSE.

For Scott's example it would look like this:

=SWITCH([Breed],
"Collie","Dog",
"Great Dane","Dog",
"Pug","Dog",
"Cat"
)