Calculating rating

[Andy84]

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.

[TABLE="class: outer_border, width: 196"]
<tbody>[TR]
[TD="align: center"]Country
[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]Top box[/TD]
[TD="align: center"]Bottom box[/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD="align: right"]1070[/TD]
[TD="align: right"]287[/TD]
[TD="align: right"]278[/TD]
[TD="align: right"]227[/TD]
[TD="align: right"]440[/TD]
[TD="align: right"]%[/TD]
[TD="align: right"]%[/TD]
[/TR]
[TR]
[TD]DE[/TD]
[TD="align: right"]563[/TD]
[TD="align: right"]124[/TD]
[TD="align: right"]130[/TD]
[TD="align: right"]107[/TD]
[TD="align: right"]234[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]FR[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]EE[/TD]
[TD="align: right"]129[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]112[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]ES[/TD]
[TD="align: right"]563[/TD]
[TD="align: right"]132[/TD]
[TD="align: right"]115[/TD]
[TD="align: right"]109[/TD]
[TD="align: right"]224[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]


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

 

[Jacob Barnett]

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:

[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