Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
This is the example of my data:
Name | Status | Age Group | Year | Substance 1 | Substance 2 | Substance 3 | Substance 4 |
A | L | 0-20 | 2010 | X | Y | Z | NA |
B | L | 0-20 | 2010 | X | Y | NA | NA |
C | L | 21-35 | 2011 | X | NA | NA | NA |
D | L | 21-35 | 2011 | Y | NA | NA | NA |
E | L | 36-50 | 2012 | X | Y | NA | NA |
F | L | 36-50 | 2012 | Z | X | NA | NA |
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.
A | L | 0-20 | 2010 | Substance 1 | X |
A | L | 0-20 | 2010 | Substance 2 | Y |
A | L | 0-20 | 2010 | Substance 3 | Z |
A | L | 0-20 | 2010 | Substance 4 | NA |
B | L | 0-20 | 2010 | Substance 1 | X |
B | L | 0-20 | 2010 | Substance 2 | Y |
B | L | 0-20 | 2010 | Substance 3 | NA |
B | L | 0-20 | 2010 | Substance 4 | NA |
C | L | 21-35 | 2011 | Substance 1 | X |
C | L | 21-35 | 2011 | Substance 2 | NA |
C | L | 21-35 | 2011 | Substance 3 | NA |
C | L | 21-35 | 2011 | Substance 4 | NA |
D | L | 21-35 | 2011 | Substance 1 | Y |
D | L | 21-35 | 2011 | Substance 2 | NA |
D | L | 21-35 | 2011 | Substance 3 | NA |
D | L | 21-35 | 2011 | Substance 4 | NA |
E | L | 36-50 | 2012 | Substance 1 | X |
E | L | 36-50 | 2012 | Substance 2 | Y |
E | L | 36-50 | 2012 | Substance 3 | NA |
E | L | 36-50 | 2012 | Substance 4 | NA |
F | L | 36-50 | 2012 | Substance 1 | Z |
F | L | 36-50 | 2012 | Substance 2 | X |
F | L | 36-50 | 2012 | Substance 3 | NA |
F | L | 36-50 | 2012 | Substance 4 | NA |
When shown in absoulte number, the things are easy. Withoud 'NA's the visuals is:
Now, I need these number to be shown as percentges of individuals in the name column. The result should be this:
X | 83.3% |
Y | 66.7% |
Z | 33.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:
Name | Status | Age Group | Year | Substance 1 | Substance 2 | Substance 3 | Substance 4 |
A | L | 0-20 | 2010 | X | Y | Z | NA |
B | L | 0-20 | 2010 | X | Y | NA | NA |
X | 100% |
Y | 100% |
Z | 50% |
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:
But when I choose only year 2010, then I get:
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.
Solved! Go to Solution.
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] ) ) )
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
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?
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?
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |