Forum Discussion

plartoo's avatar
plartoo
Regular Visitor
8 years ago
Solved

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. :)

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?

Example 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!



3 Replies