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
Nejovic
Helper I
Helper I

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
AlB
Super User
Super User

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 

 

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
AlB
Super User
Super User

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] ) )
)

This works! 

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

@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

Thank you!

I also fixed this. Thank you so much!

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? 

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.