Forum Discussion
Helper ICalculating proportions with DAX
I’m having trouble “thinking in DAX” WRT describing proportions.
I have two tables.
- A table that defines a taxonomy of categories (Dictionary)[Category] and unique issues (Dictionary)[All Issues].
- 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.
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 - Assuming that your formulas are for measures, correct?
Yes all Measures.
10 Replies
russmThanks 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
Community Champion
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.