How can I get a Count on the Column Total when I already have a Distinct Count in the Body

[miketogz]

Greetings - I am a brand new Power BI user and I am struggling with getting after a Distinct Counts for specific products purchasesd by a customer.

I created a Distinct Count for a field that has 3 values for Product Sets (Bike, Football and Video Game) and got the following results.

Customer	Bike	Football	Video Game	Total
Bob	1		1	1
Jenny	1		1	1
Rick	1	1	1	1
Sue	1			1

I like how it is showing a value of 1 in the Bike, Football and Video Game fields, but I was hoping the Total column would summarize each of these and look like what I mocked up in the next table.

Customer	Bike	Football	Video Game	Total
Bob	1		1	2
Jenny	1		1	2
Rick	1	1	1	3
Sue	1			1

Any ideas how to do this?

Thanks in advance,

Mike

[kcantor]

Try

Grand Total = [Bike]+[Football]+[Video Game]

This would be the most simplistic answer and is dependent on the columns being named correctly. Do this as a calculated field or measure instead of a column.

[miketogz]

That logic gives me the total count.

I am looking for the total count based on a distinct count for each of those 3 product sets.

Meaning - If a person bought 3 bikes, 1 football and 2 video games, I want the grand total to be 3 (based on them purchasing at least 1 product from each of the 3 product groupings). I don't want the total to be 6.

Also if a person bought 1 football and 7 video games, I want the grand total to be 2.

I am able to create "DistinctCounts" for each of the product sets to get the boolean (1,0) values, but when the grand total appears it does not carry over.

Please let me know if I am missing something with your suggestion below.

Thanks again,

Mike

[fbrossard]

Trying to undestand your problem, I've worked on this dataset :

If you create a simple DistinctCount measure 

DistinctCount of ProductSet::=CALCULATE(DISTINCTCOUNT(MyTable[Product Set]))

The calculation will only work at leaf level, because the calculation is reavalueted for each context filter :

So, if you wanna calculate the discountcount of product set by customer, and rollup using sum; you have to use a SUMMARIZE in order to create a intermediate table on the fly that distinct count the product set for each customer, and just make a SUMX on it :

Sum Of DiscountCount ProductSet over Customer:=SUMX(SUMMARIZE('MyTable';MyTable[Customer];"DCnt";DISTINCTCOUNT(MyTable[Product Set]));[DCnt])