Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I’m having trouble “thinking in DAX” WRT describing proportions.
I have two tables.
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.
Solved! Go to 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 - Assuming that your formulas are for measures, correct?
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....
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)
@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.
@russm You changed the original data? => I had 9 distinct count and all of a sudden I see 6
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 - Assuming that your formulas are for measures, correct?
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.
Yes all Measures.
Thank you for clarifying this.
User | Count |
---|---|
124 | |
106 | |
99 | |
63 | |
59 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |