Reply
Regular Visitor
Posts: 25
Registered: ‎06-26-2018
Accepted Solution

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

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

TimestampRegionService TypeService SatisfationService Provider Satisfation
7/25/2018 18:34:33New YorkBuySatisfiedVery Satisfied
7/26/2018 20:51:02LondonService / RepairVery DissatisfiedVery Dissatisfied
8/2/2018 9:23:04LondonBuyVery SatisfiedVery Satisfied
8/5/2018 17:17:52New YorkBuyVery SatisfiedVery Satisfied
8/7/2018 18:51:15LivepoolBuySatisfiedVery Satisfied
8/9/2018 2:55:11LondonBuySatisfiedVery Satisfied
8/9/2018 20:27:57LondonBuyVery SatisfiedVery Satisfied
8/27/2018 10:12:18New YorkService / RepairVery SatisfiedVery Satisfied
8/27/2018 12:48:33LondonService / RepairVery SatisfiedVery Satisfied
8/28/2018 12:28:21New YorkService / RepairSatisfiedSatisfied
8/28/2018 12:32:29New YorkService / RepairSatisfiedSatisfied
9/30/2018 13:22:22New YorkBuyNeutralSatisfied
10/1/2018 16:44:14New YorkBuyVery SatisfiedVery Satisfied
10/10/2018 16:54:45LondonBuyVery SatisfiedVery Satisfied
10/15/2018 12:30:19LondonBuyVery SatisfiedVery Satisfied
10/20/2018 19:25:44LivepoolBuyVery SatisfiedVery Satisfied
10/27/2018 16:31:51New YorkService / RepairVery DissatisfiedNeutral
10/30/2018 17:07:58New YorkBuyVery SatisfiedVery Satisfied
11/7/2018 9:57:08New YorkService / RepairVery SatisfiedVery Satisfied
11/13/2018 15:46:41LondonBuyVery DissatisfiedVery Dissatisfied
11/15/2018 10:28:17New YorkBuyNeutralNeutral
11/15/2018 17:05:47New YorkService / RepairSatisfiedSatisfied
11/20/2018 16:40:53New YorkBuyVery SatisfiedVery Satisfied
11/26/2018 15:25:31New YorkBuyVery DissatisfiedSatisfied
11/27/2018 16:46:17New YorkBuyVery SatisfiedVery Satisfied
12/2/2018 14:17:54New YorkBuyVery DissatisfiedVery Dissatisfied

Accepted Solutions
Highlighted
AlB Super Contributor
Super Contributor
Posts: 860
Registered: ‎11-12-2018

Re: How to group google form response by region then service type then service satisfaction and mont

[ Edited ]

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.

View solution in original post

AlB Super Contributor
Super Contributor
Posts: 860
Registered: ‎11-12-2018

Re: How to group google form response by region then service type then service satisfaction and mont

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 Smiley Wink

 

View solution in original post


All Replies
Regular Visitor
Posts: 25
Registered: ‎06-26-2018

Re: How to group google form response by region then service type then service satisfaction and mont

Help Please...

Highlighted
AlB Super Contributor
Super Contributor
Posts: 860
Registered: ‎11-12-2018

Re: How to group google form response by region then service type then service satisfaction and mont

[ Edited ]

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.

Community Support Team
Posts: 2,687
Registered: ‎02-06-2018

Re: How to group google form response by region then service type then service satisfaction and mont

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor
Posts: 25
Registered: ‎06-26-2018

Re: How to group google form response by region then service type then service satisfaction and mont

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 Super Contributor
Super Contributor
Posts: 860
Registered: ‎11-12-2018

Re: How to group google form response by region then service type then service satisfaction and mont

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 Smiley Wink

 

Regular Visitor
Posts: 25
Registered: ‎06-26-2018

Re: How to group google form response by region then service type then service satisfaction and mont

Hii @AlB, Thanks.