Matrix Column Sum

[PaulPalkowski]

I have a very simple table from excel

Animal	Food
Tiger	Fish
Elephant	Veggies
Bear	Fish
Monkey
Tiger	Combo
Elephant	Fish
Bear
Monkey	Veggies
Tiger
Bear	Fish
Tiger

Using a DistinctCount Measure I am using this

DC-Animals = DISTINCTCOUNTNOBLANK(Sample2[Animal])

my Matrix looks like this...

The row subtotals work, but how can I get the column subtotals to work?

I am looking for this as a result

So using count, all the totals work

But I don't want to use count,

I am not insterted if the bear had 2 servings of fish, as long as he had 1

and I do need to know if the tiger and elephant had more than 1 food type

so below is not what I am looking for

I hope this makes sense

[DataInsights · Accepted solution]

@PaulPalkowski,

Try this measure:

DC-Animals =
SUMX (
    SUMMARIZE ( Sample2, Sample2[Animal], Sample2[Food] ),
    CALCULATE ( DISTINCTCOUNTNOBLANK ( Sample2[Animal] ) )
)

[PaulPalkowski]

Perfect!!