Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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 , 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")))
Thanks, I've been able to use versions of those formulas to get what I needed
Hi,
In a simple table, please show the exact result you are expecting.
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%
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |