Forum Discussion

PaulPalkowski's avatar
PaulPalkowski
Helper II
1 year ago
Solved

Matrix Column Sum

I have a very simple table from excel

AnimalFood
TigerFish
ElephantVeggies
BearFish
Monkey 
TigerCombo
ElephantFish
Bear 
MonkeyVeggies
Tiger 
BearFish
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
 
 
 
 
Need Help
  • DataInsights's avatar
    DataInsights
    1 year ago

    PaulPalkowski,

     

    Try this measure:

     

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

     

     

2 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    1 year ago

    PaulPalkowski,

     

    Try this measure:

     

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