Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
russm
Helper I
Helper I

Calculating proportions with DAX

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.

3 ACCEPTED SOLUTIONS

@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:

distinct.png

 

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

Yes all Measures.

Calcuating Proportions2.png

View solution in original post

10 REPLIES 10
Sean
Community Champion
Community Champion

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

Thanks for the quick response!

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

 

CategoryCount of Issues% of issues by Distinct UUID
GENERAL467%
Ports350%
Startup117%
Internet/Connectivity117%

 

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:

distinct.png

 

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Sean
Community Champion
Community Champion

@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
Community Champion
Community Champion

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

Calcuating Proportions.png

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

Calcuating Proportions2.png

Thank you for clarifying this.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.