Percent and Count of a category

[aaronvincentnz]

Hello

I'm wanting to work out the percentage of all scores that are 5, 6 and 7 by (in this case) type of 'company action', but exclude the scores of '0' and '99' as part of the overall calculation. This is dummy data, but my original data is set out similarly due to unpivoting the columns, but essentially I need to only include 'unique' (or non duplicated) figures in the final calculations

e.g. with the data below, the combination of 'Keep, 5, AA' appears twice but in my calculation, I would only want to count it once. Same with 'Keep, 5, AB' and 'Keep, 5, AC' and so on.

I'm wanting to end up with something that reads "Keep scores 5-7 is 43%"

Here's some dummy data                                            

Company Action	Score	Unique ID
Keep	0	AA
Keep	0	AA
Keep	0	AA
Keep	1	AA
Keep	1	AA
Keep	1	AA
Keep	2	AA
Keep	2	AA
Keep	3	AA
Keep	3	AA
Keep	3	AA
Keep	4	AA
Keep	5	AA
Keep	5	AA
Keep	6	AA
Keep	6	AA
Keep	7	AA
Keep	7	AA
Keep	99	AA
Keep	0	AB
Keep	0	AB
Keep	0	AB
Keep	1	AB
Keep	1	AB
Keep	1	AB
Keep	2	AB
Keep	2	AB
Keep	3	AB
Keep	3	AB
Keep	3	AB
Keep	4	AB
Keep	5	AB
Keep	5	AB
Keep	6	AB
Keep	6	AB
Keep	7	AB
Keep	7	AB
Keep	99	AB
Give Away	0	AA
Give Away	0	AA
Give Away	0	AA
Give Away	1	AA
Give Away	1	AA
Give Away	1	AA
Give Away	2	AA
Give Away	2	AA
Give Away	3	AA
Give Away	3	AA
Give Away	3	AA
Give Away	4	AA
Give Away	5	AA
Give Away	5	AA
Give Away	6	AA
Give Away	6	AA
Give Away	7	AA
Give Away	7	AA
Give Away	99	AA
Give Away	0	AB
Give Away	0	AB
Give Away	0	AB
Give Away	1	AB
Give Away	1	AB
Give Away	1	AB
Give Away	2	AB
Give Away	2	AB
Give Away	3	AB
Give Away	3	AB
Give Away	3	AB
Give Away	4	AB
Give Away	5	AB
Give Away	5	AB
Give Away	6	AB
Give Away	6	AB
Give Away	7	AB
Give Away	7	AB
Give Away	99	AB

Hope the above makes sense and that someone can assist. 

Thanks

Aaron

[amitchandak · Accepted solution]

@aaronvincentnz , Try like

sumx(values(Table[Score]), calculate(distinctcount(Table[Unique ID]), filter(Table,Table[Company]="Keep")))

or

calculate(count(Table[Unique ID]), filter(Table,Table[Company]="Keep"))

or

sumx(values(Table[Score]), calculate(count(Table[Unique ID]), filter(Table,Table[Company]="Keep")))

[aaronvincentnz]

Thanks, I've been able to use versions of those formulas to get what I needed

[Ashish_Mathur]

Hi,

In a simple table, please show the exact result you are expecting.

[aaronvincentnz]

Hi Ashish

As an example, here are the results

action
'keep score'	total
0	6
1	6
2	4
3	6
4	2
5	4
6	4
7	4
99	2

The above 'totals' are unique responses (where as my previous example showed the raw data with duplicate entries, which is what happened after unpivoting the data).

In the above example, i would sum the results for 5, 6, 7 (12) and divide by 30 (which excludes the scores for '0' and '99'), giving me a result of 40%