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

DISTINCT COUNT AND GROUP BY

This has been asked many times in this forum. But I'm a newbie and haven't found any satisfying answer so far in this forum, so please bear with me. Smiley Happy

I have a table that goes 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 create a tree map in Power BI that shows Spend by Country and Advertiser **along with Distinct Brand Count per country per advertiser**. 

 

In SQL, I'd have done something like this:
SELECT Country, Advertiser, Spend, COUNT(DISTINCT Brand)
FROM my_table
GROUP BY Country, Advertiser
ORDER BY 1,2

In PowerBI, I think I have to create a new measure to find out the DISCTINCT COUNT of Brands first.? I already tried something like this to create a measure: 

DistinctBrandCount = CALCULATE(DISTINCTCOUNT(SampleDataForDashboard[Brand]), GROUPBY(SampleDataForDashboard,SampleDataForDashboard[Country]))

a few other variants that I found in this forum, but none of them seems to work so far.

Q1: Could anyone please help me with how to create a new field/metric that shows distinct count of brands for each advertiser in every country?

Q2: Assuming I get Q1 resolved, I'd like to embed the number of disctinct count in the tree map (as shown in the screenshot example below/attached). Is that possible and if so, how do I customize/modify the tree map to make DISCTINCT COUNT appear along with other metrics?

2018-05-17_1538.pngExample Tree Map that I''d like to build


I'm also attaching the test data in CSV format below in case someone would like ot test it out.
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

Thank you in advance for your suggestions/answers!



1 ACCEPTED SOLUTION

Accepted Solutions
ChrisMendoza Senior Member
Senior Member

Re: DISTINCT COUNT AND GROUP BY

@plartoo,

Using the measure from your other post  https://community.powerbi.com/t5/Desktop/Finding-DISTINCT-COUNT-of-Brands-by-Country/m-p/420940#M193...,

 

3.png

 

Seemingly, I could not find a way to show [Spend] alongside the Distinct Count so I placed [Spend] in a Tooltip.

 

3 REPLIES 3
Anonymous
Not applicable

Re: DISTINCT COUNT AND GROUP BY

@plartoo, try this:

 

Measure = countx(DISTINCT(sourceTable),sourceTable[Brand])
plartoo Frequent Visitor
Frequent Visitor

Re: DISTINCT COUNT AND GROUP BY

Thank you for your suggestion. But 

Measure = COUNTX(DISTINCT(SampleDataForDashboard), SampleDataForDashboard[Brand])

returns TOTAL number of brands in each country. Is there a way to get DISTINCT COUNT instead?

ChrisMendoza Senior Member
Senior Member

Re: DISTINCT COUNT AND GROUP BY

@plartoo,

Using the measure from your other post  https://community.powerbi.com/t5/Desktop/Finding-DISTINCT-COUNT-of-Brands-by-Country/m-p/420940#M193...,

 

3.png

 

Seemingly, I could not find a way to show [Spend] alongside the Distinct Count so I placed [Spend] in a Tooltip.

 

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 24 members 954 guests
Please welcome our newest community members: