cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Nejovic Regular Visitor
Regular Visitor

Help Needed With DAX Code

Hi All, 

This is the example of my data:

NameStatusAge GroupYearSubstance 1Substance 2Substance 3Substance 4
AL0-202010XYZNA
BL0-202010XYNANA
CL21-352011XNANANA
DL21-352011YNANANA
EL36-502012XYNANA
FL36-502012ZXNANA

 

I need to create a visual that will show the percentage of substances that were involved in deaths of individuals that are presented in 'Name' column. So, this table is about individuals who overdoses from using substances presented in 4 Substance columns. To count the total number of different substances I unpivoted these four columns. 

AL0-202010Substance 1X
AL0-202010Substance 2Y
AL0-202010Substance 3Z
AL0-202010Substance 4NA
BL0-202010Substance 1X
BL0-202010Substance 2Y
BL0-202010Substance 3NA
BL0-202010Substance 4NA
CL21-352011Substance 1X
CL21-352011Substance 2NA
CL21-352011Substance 3NA
CL21-352011Substance 4NA
DL21-352011Substance 1Y
DL21-352011Substance 2NA
DL21-352011Substance 3NA
DL21-352011Substance 4NA
EL36-502012Substance 1X
EL36-502012Substance 2Y
EL36-502012Substance 3NA
EL36-502012Substance 4NA
FL36-502012Substance 1Z
FL36-502012Substance 2X
FL36-502012Substance 3NA
FL36-502012Substance 4NA


When shown in absoulte number, the things are easy. Withoud 'NA's the visuals is:

21.png

Now, I need these number to be shown as percentges of individuals in the name column. The result should be this:

X83.3%
Y66.7%
Z33.3%

 

Also, these number should reflect the 'Age Group' and 'Year' filters applied. So, If I want to see in how many deaths substances  were involved in year 2010, the result should be:

NameStatusAge GroupYearSubstance 1Substance 2Substance 3Substance 4
AL0-202010XYZNA
BL0-202010XYNANA

 

X100%
Y100%
Z50%


I tried using this code:

Denominator = 
VAR __table = ALL(Sheet2[Name])
VAR __table1 = DISTINCT(__table)
RETURN
COUNTROWS(__table1)

And then add quick measure to divide Values in unpivoted column by Denominator, I get correct initial result:

22.png
But when I choose only year 2010, then I get:

23.png
Whic doesn't correspond to the results that I should get. The value of denominator stays the same (6), and it should be (2) when filtered by year 2010. 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Help Needed With DAX Code

Hi @Nejovic 

Try this measure with Table1[SubstanceName] in rows of a matrix visual. Table1 is the second table you show (unpivotted) and SubstanceName the last (rightmost) column on that table.

 

 

Measure =
DIVIDE (
    COUNT ( Table1[SubstanceName] );
    CALCULATE ( DISTINCTCOUNT ( Table1[Name] ); ALL ( Table1[SubstanceName] ) )
)

View solution in original post

Super User
Super User

Re: Help Needed With DAX Code

@Nejovic 

 

By number of places you mean number of decimals?

Select the measure and go to the Modeling pane. There you can select the % and choose how many decimal you want to show

View solution in original post

6 REPLIES 6
Super User
Super User

Re: Help Needed With DAX Code

Hi @Nejovic 

Try this measure with Table1[SubstanceName] in rows of a matrix visual. Table1 is the second table you show (unpivotted) and SubstanceName the last (rightmost) column on that table.

 

 

Measure =
DIVIDE (
    COUNT ( Table1[SubstanceName] );
    CALCULATE ( DISTINCTCOUNT ( Table1[Name] ); ALL ( Table1[SubstanceName] ) )
)

View solution in original post

Nejovic Regular Visitor
Regular Visitor

Re: Help Needed With DAX Code

I fixed this in a way that I created a measure:

Measure = DISTINCTCOUNT('Table'[Name])

And then just added Values as Axis, and it works nice! 

The problem that I have now is how to exclude some substances like 'NA's without affecting the value of others? 

Nejovic Regular Visitor
Regular Visitor

Re: Help Needed With DAX Code

This works! 

Is there a way now to choose the number of places and to format data labels as percentages?

Highlighted
Nejovic Regular Visitor
Regular Visitor

Re: Help Needed With DAX Code

I also fixed this. Thank you so much!

Super User
Super User

Re: Help Needed With DAX Code

@Nejovic 

 

By number of places you mean number of decimals?

Select the measure and go to the Modeling pane. There you can select the % and choose how many decimal you want to show

View solution in original post

Nejovic Regular Visitor
Regular Visitor

Re: Help Needed With DAX Code

Thank you!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 140 members 1,839 guests
Please welcome our newest community members: