FanofExcel18
Board Regular
- Joined
- Jun 7, 2018
- Messages
- 65
Issue: I have data that represents Survey's that I need to calculate in a Top 2 Box fashion for Power BI. I'm new to this and really trying to move towards this. Appreciate any help
Example: Scale of 1 to 5, only scores that matter are 4 and 5's. So if I have 10 scores: 1 = 2, 2=1, 3=1, 4=2, 5=4. My Top 2 box score is 4's(2/10) + 5's(4/10) = 60%
I can easily do this with a countif statement in Excel, but I'm trying to adapt to Power Bi and display this appropriately. =(COUNTIFS(A:A,E2,B:B,5)/COUNTIF(A:A,E2))+(COUNTIFS(A:A,E2,B:B,4)/COUNTIF(A:A,E2))
Here is sample data:
I need the results to look like this:
Example: Scale of 1 to 5, only scores that matter are 4 and 5's. So if I have 10 scores: 1 = 2, 2=1, 3=1, 4=2, 5=4. My Top 2 box score is 4's(2/10) + 5's(4/10) = 60%
I can easily do this with a countif statement in Excel, but I'm trying to adapt to Power Bi and display this appropriately. =(COUNTIFS(A:A,E2,B:B,5)/COUNTIF(A:A,E2))+(COUNTIFS(A:A,E2,B:B,4)/COUNTIF(A:A,E2))
Here is sample data:
| Team | Score |
| Team 1 | 5 |
| Team 2 | 5 |
| Team 3 | 2 |
| Team 4 | 4 |
| Team 5 | 5 |
| Team 1 | 4 |
| Team 2 | 1 |
| Team 3 | 5 |
| Team 4 | 5 |
| Team 5 | 5 |
| Team 1 | 5 |
| Team 2 | 1 |
| Team 3 | 5 |
| Team 4 | 5 |
| Team 5 | 5 |
| Team 1 | 5 |
| Team 2 | 5 |
| Team 3 | 4 |
| Team 4 | 5 |
| Team 5 | 4 |
| Team 1 | 4 |
| Team 2 | 4 |
| Team 3 | 5 |
| Team 4 | 5 |
| Team 5 | 5 |
| Team 1 | 5 |
| Team 2 | 5 |
| Team 3 | 5 |
| Team 4 | 4 |
| Team 5 | 3 |
| Team 1 | 4 |
| Team | Score |
| Team 1 | 100% |
| Team 2 | 67% |
| Team 3 | 83% |
| Team 4 | 100% |
| Team 5 | 83% |
