Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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
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
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |