Counts & Percentages for Non-Numerical Data

[afaherty]

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!

[smpa01]

@afaherty please walk me through how are you getting 50% for Baltimore-Psychology-2019 given the above data ?

[afaherty]

Ah 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.

[smpa01]

@afaherty  this is Chicago

why Psychology is 33% and History is 67%

[afaherty]

@smpa01 Yes, perfect!  What is the DAX to produce the percentages?  I will be creating a bar graph of the percentages.  Thank you!

[smpa01]

@afaherty  So Psychology for Chicago-2019 should be 25% (1/4) and History for Chaicago-2019 should be 50%(2/4) ...Do you agree?

[afaherty]

@smpa01 Ah, so Psychology is 33% because 1 person out of 3 majored in Psychology (as shown by their ID).  History is 67% because 2 people out of 3 majored in history.  (For the sake of my example, we'll say that some people had double-majors).

[smpa01 · Accepted solution]

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

[afaherty]

<Redacted - I made an error, post resolved>

[smpa01]

@afaherty  this has been going on for a while. Please start reading it from the beginning.

ID	Year	Major
1	2019	Spanish
1	2019	Education
3	2019	History
4	2019	Education
5	2019	History
5	2019	Education
5	2019	Psychology

How many unique ID's do you have ?-4

Spanish - 25% (1/4)

Education - 75% (3/4)

History - 50% (2/4)

Psy - 25% (1/4)

It is doing what it is supposed to cause you metioned before on the given data

[afaherty]

I apologize, it is difficult because I am making up this dummy data as I go along.  All I know is that it's not calculating correctly in my actual, real data.  For example, it is telling me that 1/5 is 25% when it should be 20%. It is telling me that 3/5 is 75% when it should be 60%. I apologize for wasting your time.  You have been very helpful.

[smpa01]

@afaherty  if the excerpt above is from the real data, then denominator is 4 and not 5. I have clearly explained above why it is 4 cause you previously mentione that it needs to be unique ID.

[afaherty]

It isn't, it's fake - the real denominator is 5.  I will attempt to figure it out.  Thanks again,

[afaherty]

<Redacted - I made an error, post resolved>

[smpa01]

@afaherty  provide the screenshot from your end of the measure and the viz

[afaherty]

Maybe @smpa01 ?  

Been attempting this for a couple of days now. 😓  Sincerely thank everyone for their help.

[VahidDM]

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/_B

download this file:

https://gofile.io/d/jIJAeq

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/

[afaherty]

@VahidDM Thank you, though it doesn't seem that the measure is working.  It's producing 100% for each row.