# Calculating rating

#### Andy84

##### New Member
I've tried to use CALCULATE and other functions to group survey participants into groups, but failed so far. It's probably something very simple, but I just can't grasp the concept here as I'm fairly new to PowerPivot.

Here's a quick table on survey results I get from survey tool. Participant country on the left and their rating on a 5 point scale on the right, where number is amount of participants giving a specific rating.

 Country 1 2 3 4 5 Top box Bottom box US 1070 287 278 227 440 % % DE 563 124 130 107 234 FR 5 6 3 2 EE 129 56 44 40 112 ES 563 132 115 109 224

<tbody>
</tbody>

What I would need is to add 2 columns where it would calculate % of users from each country who rated 1-2 (bottom box) and column calculating % of participants rating 4-5 (top box). These groups would automatically update with the pivot table if I use slicers.

Any tips or guides would be appreciated

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Andy, welcome to the revolution!

Is the table above how your source data comes out? If so then the basic cause of your issue is that its not really in the format PowerPivot likes to work with which is a column based structure as opposed to a 'cross tab'.

DAX could be used to solve the problem with your dataset above - you would have to write 5 simple measures that sums each of the results columns e.g. [1s] = sum(table1[1]) and so on..... then use those 5 to create two further measures for the percentages e.g. [Top Box] =([1s]+[2s]+[3s])/([1s]+[2s]+[3s]+[4s]+[5s]). This would be way easier to solve in Excel and you would be missing out on the true potential and usefulness of PowerPivot!

If you can get your data in a format that looks a bit more like the table below then you will need CALCULATE() to get your answer and you will start to see the power of DAX.

Under these circumstances you would need a measure to count the rows e.g. [Rating Count] = COUNT(table1[Rating]). To recreate your table you would drag this into the Pivot with countries as ROWS and Rating as Columns.

From here your [Top Count] measure would then adjust the first measure just for surveys where the rating was 3 or less: =CALCULATE([Rating Count],table[Rating]<=3) and then divide this by a further manipulated version of [Rating Count] where the filter context is opened so out that the rating context is removed: =CALCULATE([Rating Count],ALL(Fact[Rating])). You final formula would then be:

Code:
``[Top Count] = CALCULATE([Rating Count],table[Rating]<=3) / CALCULATE([Rating Count],ALL(Fact[Rating]))``

DAX isn't intuitive and takes a bit of work to learn the basics however once you get over the hump it gets easier and if you are thinking that this was a lot of effort to do something that you could do in Excel in 30 seconds then you should consider that PowerPivot would happily handle 10s of millions of surveys and provide results near instantaneously.

Jacob

Replies
13
Views
677
Replies
3
Views
2K
Replies
0
Views
193
Replies
1
Views
1K
Replies
3
Views
127

1,203,094
Messages
6,053,505
Members
444,667
Latest member
KWR21

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back