Calculating proportions with DAX

[russm]

I’m having trouble “thinking in DAX” WRT describing proportions.

I have two tables.

1. A table that defines a taxonomy of categories (Dictionary)[Category] and unique issues (Dictionary)[All Issues].
2. A table of questionnaire responses. Each response can be parsed across multiple issues.

• Among many other columns, the table includes one named (Verbatims)[Unpivoted Issues].
• It also includes a unique respondent ID (Verbatims)[UUID].

Dictionary

Category	Issue
GENERAL	General - OTHER Issues
GENERAL	General – Vague issues
Startup	Startup - Other Issues
Ports	USB Port - Physical damage
Internet/Connectivity	Wi-Fi - Vague Issue

Verbatims

UUID	Unpivoted_Issues
5a6b5a614e593f722a24736c39	General - OTHER Issues
3e2b46627b51763b556b5b2d2e	General – Vague issues
654e434370456f4c2d6455496c	General - OTHER Issues
654e434370456f4c2d6455496c	USB Port - Physical damage
654e434370456f4c2d6455496c	Startup - Other Issues
2d3e5e557c4e4373592754275f	Wi-Fi - Vague Issue
6a7b3255424430656a6e7a5329	USB Port - Physical damage
6a7b3255424430656a6e7a5329	General - OTHER Issues
4b394832746520534c2165794f	USB Port - Physical damage

I need to produce visualizations that show the proportions of issues within categories by unique respondents.

I didn’t have trouble coming up with a visualization for % of issues by unique respondents or count of issues within their categories but I can’t seem to get my arms around this one.

[Greg_Deckler · Accepted solution]

@russm - With the data you posted, I created 2 new columns in Dictionary:

Count of Issues = CALCULATE(COUNTROWS(DISTINCT(Verbatims[UUID])),RELATEDTABLE(Verbatims))

Percent of Issues = [Count of Issues] / COUNTROWS(DISTINCT(Verbatims[UUID]))

I get the visualization:

I think I like @Sean's better as it shows the total unique (6) and the percentage comes out to 100%. I guess it is just what makes more sense to your users, not all of the issues adding up to 9 or having 150%. Or, just turn off the totals row and don't tell them.

[Greg_Deckler · Accepted solution]

@Sean - Assuming that your formulas are for measures, correct?

[Sean]

@russm

So in this example - is this the result you are looking for?

GENERAL => General - OTHER Issues => 50%

                  => General - Vague Issues  => 50%

GENERAL =>                                         => 100% 

and so on....

[russm]

Thanks for the quick response!

Sort of.  What I'm after is more like this (count of issues included for clarity):

Category	Count of Issues	% of issues by Distinct UUID
GENERAL	4	67%
Ports	3	50%
Startup	1	17%
Internet/Connectivity	1	17%

Where: Distinctcount(Verbatims)[UUID] = 6

(Apologies for the hex code in the original post.  I should've just used names)

[Greg_Deckler · Accepted solution]

@russm - With the data you posted, I created 2 new columns in Dictionary:

Count of Issues = CALCULATE(COUNTROWS(DISTINCT(Verbatims[UUID])),RELATEDTABLE(Verbatims))

Percent of Issues = [Count of Issues] / COUNTROWS(DISTINCT(Verbatims[UUID]))

I get the visualization:

I think I like @Sean's better as it shows the total unique (6) and the percentage comes out to 100%. I guess it is just what makes more sense to your users, not all of the issues adding up to 9 or having 150%. Or, just turn off the totals row and don't tell them.

[Sean]

@russm You changed the original data? => I had 9 distinct count and all of a sudden I see 6

[russm]

Guilty. I realized right after I posted that the original was not illustrating my concern the way I wanted,  I'd thought I caught it before I had any views. 

[Sean]

@russm In any event here is your solution... After data refresh...

[Greg_Deckler · Accepted solution]

@Sean - Assuming that your formulas are for measures, correct?

[russm]

Thank you.  This does work against my example.  The ratios are a little off when applied to my actual data model.  I think I have some ALLEXCEPT work to do which goes beyond the scope of the original ask.

[Sean · Accepted solution]

Yes all Measures.

[russm]

Thank you for clarifying this.