POWER BI DAX: best solution of how not to duplicate to merge two tables and calculate the metric

[zakkyang]

I have the following 2 tables:

Table 1
Date	Brand	Segment	Daily Click User
1/1/2020	A	Segment1	100
1/1/2020	A	Segment2	200
1/1/2020	A	Segment3	454
1/1/2020	A	Segment4	534
1/2/2020	A	Segment1	546
1/2/2020	A	Segment2	675
1/2/2020	A	Segment3	876
1/2/2020	A	Segment4	876
1/1/2020	B	Segment1	987
1/1/2020	B	Segment2	54345
1/1/2020	B	Segment3	45425
1/1/2020	B	Segment4	43566
1/2/2020	B	Segment1	4786545
1/2/2020	B	Segment2	453543
1/2/2020	B	Segment3	6545645
1/2/2020	B	Segment4	654654

Table 2
Date	Brand	Total Daily Brand Population
1/1/2020	A	10000000
1/2/2020	A	20000000
1/1/2020	B	3000000000
1/2/2020	B	3100000000

The target metric calculation logic is in below: 

Date	Brand	Segment	Daily Click User	Brand Population	Brand Penetration%
1/1/2020	A	Segment1	100	10000000	?
1/1/2020	A	Segment2	200	10000000	?
1/1/2020	A	Segment3	454	10000000	?
1/1/2020	A	Segment4	534	10000000	?
1/2/2020	A	Segment1	546	20000000	?
1/2/2020	A	Segment2	675	20000000	?
1/2/2020	A	Segment3	876	20000000	?
1/2/2020	A	Segment4	876	20000000	?
1/1/2020	B	Segment1	987	3000000000	?
1/1/2020	B	Segment2	54345	3000000000	?
1/1/2020	B	Segment3	45425	3000000000	?
1/1/2020	B	Segment4	43566	3000000000	?
1/2/2020	B	Segment1	4786545	3100000000	?
1/2/2020	B	Segment2	453543	3100000000	?
1/2/2020	B	Segment3	6545645	3100000000	?
1/2/2020	B	Segment4	654654	3100000000	?

The Brand Penetration Rate is calculated by Clicker Users/Total Brand Population.

It is tempting to use the dax below:

Total Click = SUM(table1[Daily Click User])
Total Brand = SUM(table2[Total Daily Brand Population]) 
Brand Penetration = [Total Click]/[Total Brand]\

However, I am worried that it will result in duplicated values when aggregating the daily click user by segments, the total brand population will also be duplicated.

My hope is to flexibly know the Brand Penetration Rate by segments slicer or overall.

So finally, we can see the trend visualization by either any segments or All.

Thank you.

[v-yalanwu-msft · Accepted solution]

Hi， @zakkyang ；

You could merge two tables in power query first ,and then create a measure.

1.merge two tables by two columns.

2.create a measure.

Brand Penetration = 
VAR _sumclick =
    CALCULATE (
        SUM ( [Daily Click User] ),
        ALLEXCEPT ( Table1, Table1[Brand], Table1[Date] ))
RETURN
    DIVIDE(MAX ( [Table2.Total Daily Brand Population] ) ,_sumclick)

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.