Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have 3 queries:
Pages
- page_id
- title
- ...
Hits
- hit_id
- page_id
- date
- ...
Form_submissions
- submissions_id
- page_id
- date
- ...
I'd like to create a bar chart that shows the percentage of submissions compared to hits, to each page, over months.
Something like this:
Page title | Hits | % submissions | Year-Month
Page 1 | 500 | 10% | 2017-01
Page 2 | 200 | 30% | 2017-01
I have no idea how to start.
Anyone?
Thanks
Hi @dbastreghi,
You need to add a calendar table and also make a relationship between the tables you have.
1 - Create a calendar table:
Calendar = CALENDAR("01/01/2017","31/12/2017")
Replace the dates by whatever values you want, you can also add a moth-year column with the following formula:
Month = FORMAT('Calendar'[Date],"mm - yyyy")
2 - Create relationship between the table you have:
3 - Add the values you need to your chart / table formatting the Submissions as a % of grande total:
Regards,
Mfelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi Mfelix, thank you very much.
I did as you suggested, but I think "percent of grand total" shows the percentage releated to self distribution. I need the percentage related to the hits total.
Thanks again.
Hi @dbastreghi,
You want to calculate the percentage of submissions ID based on the number of Hits? Or do you want the % of Hits taking into account total number of hits?
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsI want calculate the percentage of submissions ID based on the number of Hits, per page.
Could you please ptovide us some sample data, so that we can make further analysis.
Regards,
Charlie Liao
Hi v-caliao-msft,
Query Pages
page_id | title 1 | Web Page X
2 | Web Page Y
Query Hits
hit_id | page_id | date
1 | 1 | 2017-08-01 10:05:02
2 | 1 | 2017-08-02 11:01:55
3 | 1 | 2017-08-05 09:30:25
4 | 1 | 2017-07-12 12:01:55
5 | 2 | 2017-08-01 10:05:02
6 | 2 | 2017-08-02 11:01:55
7 | 2 | 2017-08-05 09:30:25
8 | 2 | 2017-07-12 12:01:55
Query form_submissions
submissions_id | page_id | date
1 | 1 | 2017-08-01 10:15:01
2 | 1 | 2017-08-02 11:09:48
3 | 1 | 2017-08-05 09:45:15
7 | 2 | 2017-08-05 09:35:25
8 | 2 | 2017-07-12 12:05:12
For these data, the expected result is:
Page title | Year-Month | Count(Hits) | Count(submissions) | % submissions Web Page X | 2017-07 | 1 | 0 | 0%
Web Page X | 2017-08 | 3 | 3 | 100% Web Page Y | 2017-07 | 1 | 1 | 100%
Web Page Y | 2017-08 | 3 | 1 | 33.3%
I have tested it on my local environment, the steps below are for you reference.
Results.
Regards,
Charlie Liao
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
97 | |
75 | |
65 | |
53 |
User | Count |
---|---|
144 | |
103 | |
98 | |
85 | |
64 |