How to group google form response by region then service type then service satisfaction and month?

[ranaanees]

Please see my dataset.

I want to visualize it in some charts in Power BI.

I want to see group it by

1. Month

2. Then Region

3. Then Service Type

4. Then Service Satisfation

Meaning in August in London how much is Service Satisfation for Buy and Service/Repair.

Same for Service Provider Satifation. in pecentage.

Dataset

Timestamp	Region	Service Type	Service Satisfation	Service Provider Satisfation
7/25/2018 18:34:33	New York	Buy	Satisfied	Very Satisfied
7/26/2018 20:51:02	London	Service / Repair	Very Dissatisfied	Very Dissatisfied
8/2/2018 9:23:04	London	Buy	Very Satisfied	Very Satisfied
8/5/2018 17:17:52	New York	Buy	Very Satisfied	Very Satisfied
8/7/2018 18:51:15	Livepool	Buy	Satisfied	Very Satisfied
8/9/2018 2:55:11	London	Buy	Satisfied	Very Satisfied
8/9/2018 20:27:57	London	Buy	Very Satisfied	Very Satisfied
8/27/2018 10:12:18	New York	Service / Repair	Very Satisfied	Very Satisfied
8/27/2018 12:48:33	London	Service / Repair	Very Satisfied	Very Satisfied
8/28/2018 12:28:21	New York	Service / Repair	Satisfied	Satisfied
8/28/2018 12:32:29	New York	Service / Repair	Satisfied	Satisfied
9/30/2018 13:22:22	New York	Buy	Neutral	Satisfied
10/1/2018 16:44:14	New York	Buy	Very Satisfied	Very Satisfied
10/10/2018 16:54:45	London	Buy	Very Satisfied	Very Satisfied
10/15/2018 12:30:19	London	Buy	Very Satisfied	Very Satisfied
10/20/2018 19:25:44	Livepool	Buy	Very Satisfied	Very Satisfied
10/27/2018 16:31:51	New York	Service / Repair	Very Dissatisfied	Neutral
10/30/2018 17:07:58	New York	Buy	Very Satisfied	Very Satisfied
11/7/2018 9:57:08	New York	Service / Repair	Very Satisfied	Very Satisfied
11/13/2018 15:46:41	London	Buy	Very Dissatisfied	Very Dissatisfied
11/15/2018 10:28:17	New York	Buy	Neutral	Neutral
11/15/2018 17:05:47	New York	Service / Repair	Satisfied	Satisfied
11/20/2018 16:40:53	New York	Buy	Very Satisfied	Very Satisfied
11/26/2018 15:25:31	New York	Buy	Very Dissatisfied	Satisfied
11/27/2018 16:46:17	New York	Buy	Very Satisfied	Very Satisfied
12/2/2018 14:17:54	New York	Buy	Very Dissatisfied	Very Dissatisfied

[AlB · Accepted solution]

Hi @ranaanees

It depends on what kind of visual you prefer but one quick option would be to: 

1. Place a 100% stacked column chart in your report

2. Place the columns of your table in the chart fields:

   - In axis:      Region and Month

   - In legend:  Service satisfaction

   - In value:     Count of Service Satisfaction  

 3. Place Service Type in a slicer  

Before you carry out the steps above, create a Calendar table and link it to your data table so that you can filter by month conveniently. Since your Timestamp column includes time, it is best to create a new calculated column in your table with only the date, as in:

Table1[Timestamp2] = Table1[Timestamp].[Date]     

and use this new column to create the relationship with the 'Date' table.

[v-piga-msft]

Hi @ranaanees,

Have you solved your problem?

If you have solved, please accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please share your desired output so that we could help further on it.

Best Regards,

Cherry

[ranaanees]

Help Please...

[AlB · Accepted solution]

Hi @ranaanees

It depends on what kind of visual you prefer but one quick option would be to: 

1. Place a 100% stacked column chart in your report

2. Place the columns of your table in the chart fields:

   - In axis:      Region and Month

   - In legend:  Service satisfaction

   - In value:     Count of Service Satisfaction  

 3. Place Service Type in a slicer  

Before you carry out the steps above, create a Calendar table and link it to your data table so that you can filter by month conveniently. Since your Timestamp column includes time, it is best to create a new calculated column in your table with only the date, as in:

Table1[Timestamp2] = Table1[Timestamp].[Date]     

and use this new column to create the relationship with the 'Date' table.

[ranaanees]

Hii @AlB,

Your solution is perfect and solved my problem.

I have one more question from the same dataset.

In my Dataset there is Service Satisfation column and I want the following formula,

Satisfied = Satisfied + Very Satisfied

Dissatisfied = Dissatisfied + Very Dissatisfied 

Neutral = Neutral

Is this possible? Please guide how to achieve it. 

Thanks,

[AlB · Accepted solution]

Hi @ranaanees

I think the quickest would be  to create a new calculated column in which you do the mapping that you need, i.e.

If Satisfied OR Very Satisfied

   then Satisfied

etc.

then you can use that new column in lieu of the "Service Satisfaction" column, in the same way.

Cheers

As pointed out earlier, mark the post as solved when you think it is solved.

and kudos at your discretion

[ranaanees]

Hii @AlB, Thanks.