topic Re: Counts & Percentages for Non-Numerical Data in DAX Commands and Tips

Counts & Percentages for Non-Numerical Data

afaherty — Fri, 17 Dec 2021 19:34:57 GMT

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!

Re: Counts & Percentages for Non-Numerical Data

VahidDM — Thu, 16 Dec 2021 21:57:24 GMT

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/

Re: Counts & Percentages for Non-Numerical Data

afaherty — Fri, 17 Dec 2021 15:30:37 GMT

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

Re: Counts & Percentages for Non-Numerical Data

afaherty — Fri, 17 Dec 2021 18:59:24 GMT

Maybe smpa01 ?

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

Re: Counts & Percentages for Non-Numerical Data

smpa01 — Fri, 17 Dec 2021 19:15:58 GMT

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

Re: Counts & Percentages for Non-Numerical Data

afaherty — Fri, 17 Dec 2021 19:27:29 GMT

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.

Re: Counts & Percentages for Non-Numerical Data

smpa01 — Fri, 17 Dec 2021 19:58:53 GMT

afaherty this is Chicago

why Psychology is 33% and History is 67%

Re: Counts & Percentages for Non-Numerical Data

afaherty — Fri, 17 Dec 2021 20:00:29 GMT

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

Re: Counts & Percentages for Non-Numerical Data

smpa01 — Fri, 17 Dec 2021 20:02:26 GMT

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

Re: Counts & Percentages for Non-Numerical Data

afaherty — Fri, 17 Dec 2021 20:09:50 GMT

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

Re: Counts & Percentages for Non-Numerical Data

smpa01 — Fri, 17 Dec 2021 20:26:00 GMT

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 )

Re: Counts & Percentages for Non-Numerical Data

afaherty — Fri, 17 Dec 2021 21:46:03 GMT

Re: Counts & Percentages for Non-Numerical Data

smpa01 — Fri, 17 Dec 2021 20:39:35 GMT

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

Re: Counts & Percentages for Non-Numerical Data

afaherty — Fri, 17 Dec 2021 21:45:42 GMT

Re: Counts & Percentages for Non-Numerical Data

smpa01 — Fri, 17 Dec 2021 21:27:00 GMT

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

Re: Counts & Percentages for Non-Numerical Data

afaherty — Fri, 17 Dec 2021 21:31:52 GMT

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.

Re: Counts & Percentages for Non-Numerical Data

smpa01 — Fri, 17 Dec 2021 21:41:36 GMT

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.

Re: Counts & Percentages for Non-Numerical Data

afaherty — Fri, 17 Dec 2021 21:42:49 GMT

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