Percentage with 2 tables

[DarioSantos]

Hi,

I have 2 tables with traffic data from 3 sites.

One with data by day, device and total PV on site.

data	site	pv	device
10-out	a	897238	destkop
10-out	a	213124	mobile
10-out	b	324324	destkop
10-out	b	2342422	mobile
10-out	c	3242342	destkop
10-out	c	2342332	mobile
09-out	a	7892316	destkop
09-out	a	21469	mobile
09-out	b	12634912	destkop
09-out	b	12839	mobile
09-out	c	12764381	destkop
09-out	c	128498	mobile

Other with day, source, site, device and PV.

data	source	site	pv	device
10-out	a	b	198	destkop
10-out	a	b	219	mobile
10-out	a	c	132	destkop
10-out	a	c	453	mobile
10-out	b	a	324	destkop
10-out	b	a	423	mobile
10-out	b	c	321	destkop
10-out	b	c	43	mobile
10-out	c	a	321	destkop
10-out	c	a	43	mobile
10-out	c	b	4	destkop
10-out	c	b	56	mobile
09-out	a	b	1	destkop
09-out	a	b	213	mobile
09-out	a	c	324	destkop
09-out	a	c	3456	mobile
09-out	b	a	4	destkop
09-out	b	a	56	mobile
09-out	b	c	32	destkop
09-out	b	c	8	mobile
09-out	c	a	8	destkop
09-out	c	a	4	mobile
09-out	c	b	6	destkop
09-out	c	b	2	mobile

I need have a % of traffic on one site came from other site.

For example % of traffic on site A came from site B. on this case (324+423+4+56)/(897238+213124+7892316+21469) 

How i can make this mesure?

I try create a aux column in 2 tables with day&site&device and related this aux column but with the mesure calculate(sum(table1[PV])/sum(table2[PV])) the power bi sum all valueson table 2 and not only  the values with same parameters.

[v-shex-msft · Accepted solution]

Hi @DarioSantos,

According to your description, you want to get result about “total pv of same source and same site in table 2” / “total pv of same site in table 1”, right?

If it is a case, you can refer to below measure to achieve your requirement:

Measure:
Percent =
var currSite=LASTNONBLANK(Sheet2[site],Sheet2[site])
var currSource=LASTNONBLANK(Sheet2[source],Sheet2[source])
return
SUMX(FILTER(ALL(Sheet2),Sheet2[site]=currSite&&Sheet2[source]=currSource),Sheet2[pv])/SUMX(FILTER(ALL(Sheet1),Sheet1[site]=currSite),Sheet1[pv])

Result visual:
 

Regards,
Xiaoxin Sheng

[Anonymous]

I think your challenges have to do w/ the way you "shaped" your data.  My first thought was "can't we combine these 2 tables into 1?" -- and I still have that question.  If that doesn't make sense, I suspect it will help to have some separate lookup tables, for site/date/device... that you can link to BOTH of your tables.

Ignoring that, ... 

Raw % := DIVIDE(SUM(Table1[PV]),Sum(Table2[PV]))

Filltered % := CALCULATE([Raw %], FILTER(Table2, CONTAINS(Table1, Table1[site], Table2[site], Table1[device], Table2[device]))

I totally didn't test that, but it makes sense to me at least 🙂  Filter table 2, down to just where that rows site/device are in Table 1's current context.