cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

Percent and Count of a category

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 ActionScoreUnique ID
Keep0AA
Keep0AA
Keep0AA
Keep1AA
Keep1AA
Keep1AA
Keep2AA
Keep2AA
Keep3AA
Keep3AA
Keep3AA
Keep4AA
Keep5AA
Keep5AA
Keep6AA
Keep6AA
Keep7AA
Keep7AA
Keep99AA
Keep0AB
Keep0AB
Keep0AB
Keep1AB
Keep1AB
Keep1AB
Keep2AB
Keep2AB
Keep3AB
Keep3AB
Keep3AB
Keep4AB
Keep5AB
Keep5AB
Keep6AB
Keep6AB
Keep7AB
Keep7AB
Keep99AB
Give Away0AA
Give Away0AA
Give Away0AA
Give Away1AA
Give Away1AA
Give Away1AA
Give Away2AA
Give Away2AA
Give Away3AA
Give Away3AA
Give Away3AA
Give Away4AA
Give Away5AA
Give Away5AA
Give Away6AA
Give Away6AA
Give Away7AA
Give Away7AA
Give Away99AA
Give Away0AB
Give Away0AB
Give Away0AB
Give Away1AB
Give Away1AB
Give Away1AB
Give Away2AB
Give Away2AB
Give Away3AB
Give Away3AB
Give Away3AB
Give Away4AB
Give Away5AB
Give Away5AB
Give Away6AB
Give Away6AB
Give Away7AB
Give Away7AB
Give Away99AB

 

Hope the above makes sense and that someone can assist. 

Thanks

Aaron

1 ACCEPTED SOLUTION
Super User IV
Super User IV

@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")))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

4 REPLIES 4
Super User IV
Super User IV

@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")))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

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

Super User III
Super User III

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish

As an example, here are the results

action 
'keep score'total
06
16
24
36
42
54
64
74
992

 

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%

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors