Forum Discussion
Percentage with 2 tables
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.
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
2 Replies
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.
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