How to analyze two tables

[yohay]

I have the following tables:

users events:

  Event                  Date

page1	2016/1/14 10:22:22PM
page1	2016/1/14 10:22:22PM
page1	2016/1/14 10:22:22PM
page1	2016/1/14 10:22:22PM
page1	2016/2/14 10:22:22PM
page2	2016/2/14 10:22:22PM
page1	2016/2/14 10:22:22PM

user actions:

  Action                 Date

action1	2016/2/14 10:22:22PM
action2	2016/2/14 9:11:22PM
action1	2016/2/14 11:24:22PM
action1	2016/1/14 11:24:22PM
action1	2016/1/14 11:24:22PM
action1	2016/1/14 11:24:22PM
action2	2016/1/14 11:26:22PM

So a distinct count of the page1 per month would be:

 Year       Count

Feb	2
Jan	4

The sum of action1 per month would be:

  Year     Count

Feb	2
Jan	3

I would like to achieve the following:

(distinct count action1 at Feb2016) / (sum of event1 at Feb2016) => 2/2 => 1

(distinct count action1 at Jan2016) / (sum of event1 at Jan2016) => 3/4 => 0.75

 Year      %of actions per event

Feb	1
Jan	0.75

I want to chart that in power bi.

Any help would be appreciated.

[v-huizhn-msft · Accepted solution]

Hi @yohay,

For your requirement, you should summarize the two tables, create the relationship between the new summarized tables. Then create calculated column to get the expected result.

I try to reproduce your scenario as follows.

Create new table using the formulas, and get the two new table shown in the following screenshot.

Table = SUMMARIZE(Table1,Table1[Month],"Page1",CALCULATE(COUNTA(Table1[Event]),FILTER(Table1,Table1[Event]="page1")),"Page2",CALCULATE(COUNTA(Table1[Event]),FILTER(Table1,Table1[Event]="page2")))

Table 2 = SUMMARIZE(Table2,Table2[Month],"Action1",CALCULATE(COUNTA(Table2[Action]),Table2[Action]="action1"),"Action2",CALCULATE(COUNTA(Table2[Action]),Table2[Action]="action2"))

 

 
Then create the relationship between the Table and Table 2.

 
Create calculated columns using the formulas below, and you get the expected result.

Action1 = RELATED('Table 2'[Action1])
Action2 = RELATED('Table 2'[Action2])
Percentage = 'Table'[Action1]/'Table'[Page1]


 
If you have any other issue, please feel free to ask.

Best Regards,
Angelia

[yohay]

Thank you very much Angelia @v-huizhn-msft 

I will follow your solution!