Forum Discussion
Helper VCounts & Percentages for Non-Numerical Data
Hello,
Can someone help me figure this out? This is all fake data but is set up in the same manner as my real data.
This is unpivoted:
| ID | Graduation Year | Majors | City |
| 1 | 2020 | Education | Baltimore |
| 1 | 2020 | Spanish | Baltimore |
| 1 | 2020 | Psychology | Baltimore |
| 2 | 2019 | Psychology | Chicago |
| 3 | 2019 | Education | Philadelphia |
| 3 | 2019 | Spanish | Philadelphia |
| 4 | 2020 | Education | Madison |
| 4 | 2020 | Spanish | Madison |
| 4 | 2020 | History | Madison |
| 5 | 2019 | Education | Athens |
| 6 | 2019 | Education | Albuquerque |
| 6 | 2019 | Spanish | Albuquerque |
| 7 | 2020 | Education | Tampa |
| 7 | 2020 | Spanish | Tampa |
| 7 | 2020 | History | Tampa |
| 8 | 2019 | Education | Newport News |
| 9 | 2019 | Education | Dover |
| 9 | 2019 | Spanish | Dover |
| 10 | 2020 | History | Sacramento |
| 11 | 2020 | Education | Baltimore |
| 11 | 2020 | Spanish | Baltimore |
| 12 | 2019 | History | Chicago |
| 13 | 2019 | History | Chicago |
| 13 | 2019 | Education | Chicago |
1.) What I need to do is first find the totals and there will be a slicer for city.
2.) Secondly, I need to find the percentages of majors by city (city will still be the slicer) by graduation year. So, for example, for Chicago, the % of Psychology majors is 33%. For Chicago, the % of history majors is 67%.
I hope that makes sense. Thanks to anyone who can help!
afaherty please give this a try and let me know if this gives you what you need
Measure = VAR _numerator = DISTINCTCOUNT ( 'Table 1'[ID] ) VAR _selectedCity = ALLSELECTED ( 'Table 1'[City] ) VAR _denominator = CALCULATE ( DISTINCTCOUNT ( 'Table 1'[ID] ), TREATAS ( _selectedCity, 'Table 1'[City] ), REMOVEFILTERS ( 'Table 1'[Majors] ) ) RETURN DIVIDE ( _numerator, _denominator )
17 Replies
- VahidDM
Super User
Hi afaherty
For the first question you can use the Matrix visual and set it as below:
For the second question use this measure:
Measure =Var _A = count('Table'[ID])Var _B = CALCULATE(count('Table'[ID]),REMOVEFILTERS('Table'[City]))return_A/_Bdownload this file:If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/ 
afahertyAh so sorry, I must have been staring at it for far too long. I edited the post to say: "for Chicago, the % of Psychology majors is 33%." I always attempt to google my question before coming here, and I think I just got all mixed up after reading so much.
