Count different queries and get percentage from each one

[dbastreghi]

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

[MFelix]

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:

• Pages - Form_Submission (one to many relationship)
• Pages - Hits (one to many relationship)
• Calendar - Form_Submissions (one to many relationship)
• Calendar - Hits (one to many relationship)

3 - Add the values you need to your chart / table formatting the Submissions as a % of grande total:

Regards,

Mfelix

[dbastreghi]

Hi 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.

[MFelix]

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

[dbastreghi]

I want calculate the percentage of submissions ID based on the number of Hits, per page.

[v-caliao-msft]

@dbastreghi,

Could you please ptovide us some sample data, so that we can make further analysis.

Regards,

Charlie Liao

[dbastreghi]

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%  

[v-caliao-msft]

@dbastreghi,

I have tested it on my local environment, the steps below are for you reference.

1. Create a table by using the DAX below.
   Table = ADDCOLUMNS(FILTER(CALENDAR(DATE(2017,7,1),DATE(2017,8,31)),DAY([Date])=1),"Year-Month",FORMAT([Date],"YYYY-MM"))
2. Create Year-month column in Hits table and Submissions table
   Year-Month = FORMAT(Hits[date],"YYYY-MM")
3. Create realationships between new created table and Hits table and Submissions table.
4. Create count measure in Hits table and Submissions table
   Measure = COUNT(Hits[hit_id])
   Measure 2 = COUNT(Submissions[page_id])
5. Create percentage column in Pages table
   Measure 3 = IF(ISBLANK([Measure 2]),0,[Measure 2]/[Measure])

Results.

Regards,

Charlie Liao