cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
plartoo Frequent Visitor
Frequent Visitor

Finding DISTINCT COUNT of Brands by Country

Hi all,

I have a table like this:

Country, Advertiser, Brand, Spend
C1, A1, B1, 10
C1, A1, B2, 5
C1, A2, B3, 0
C1, A2, B4, 20
C2, A1, B1, 8
C2, A1, B5, 7
C2, A2, B6, 4
C2, A2, B3, 3
C2, A2, B7, 2
C2, A3, B8, 9

I'd like to find the DISTINCT COUNT of Brands by Country as below:

C1 => 4
C2 => 6


How do I create a Measure in Power BI Desktop to achieve this?

Thank you for your answers/suggestions!

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisMendoza Established Member
Established Member

Re: Finding DISTINCT COUNT of Brands by Country

hello @plartoo,

 

Possibly I'm over simplifying your problem however I've accomplished your desired output with 2 different measures:

 

Measure = CALCULATE(COUNTROWS(Table3),DISTINCT(Table3[Country]))

Measure 2 = COUNTROWS(Table3)

Also I used Count of Brand in the Values area as well.

 

2.PNG

 

Measure 3 = CALCULATE(DISTINCTCOUNT(Table3[Brand]), GROUPBY(Table3,Table3[Country]))

Was taken from https://stackoverflow.com/questions/50414271/creating-a-measure-that-returns-distinct-count-by-count... which looks exactly like your question.

 

DISTINCTCOUNT in Measure 3 is returning 8 because 'B1' & 'B3' is only counted once over the entire data set.

3 REPLIES 3
ChrisMendoza Established Member
Established Member

Re: Finding DISTINCT COUNT of Brands by Country

hello @plartoo,

 

Possibly I'm over simplifying your problem however I've accomplished your desired output with 2 different measures:

 

Measure = CALCULATE(COUNTROWS(Table3),DISTINCT(Table3[Country]))

Measure 2 = COUNTROWS(Table3)

Also I used Count of Brand in the Values area as well.

 

2.PNG

 

Measure 3 = CALCULATE(DISTINCTCOUNT(Table3[Brand]), GROUPBY(Table3,Table3[Country]))

Was taken from https://stackoverflow.com/questions/50414271/creating-a-measure-that-returns-distinct-count-by-count... which looks exactly like your question.

 

DISTINCTCOUNT in Measure 3 is returning 8 because 'B1' & 'B3' is only counted once over the entire data set.

Super User
Super User

Re: Finding DISTINCT COUNT of Brands by Country

Here is a single measure solution. Put this in a table along with Country.

 

Measure = 
VAR __tmpTable = SUMMARIZE(brands,brands[Country],"__Brands",DISTINCTCOUNT(brands[ Brand]))
RETURN MAXX(__tmpTable,[__Brands])

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


plartoo Frequent Visitor
Frequent Visitor

Re: Finding DISTINCT COUNT of Brands by Country

Thank you, Dozer. This solution works! I'll ask another follow-up question about how to make this distinct count value appear in a Tree-map soon. Smiley Happy