Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
77 | |
68 | |
54 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |