Show As a Percentage of Unique Values in a Column

[Nejovic]

Hi everyone, 

This is the structure of my data:



Since I needed all Substances in one columns, in order to derive statistics, I had to unpivot 4 Substances columns:



When I derive statistics about substances using Clastered Bar Chart, and show Value as Percent of grand total I get next results (when I exclude 'NA's which stands for Not Available)

This is OK, since this calculates substances as a percent of all substances, after excluding NAs. But this is not what I need. I need each substance to be shown as a percent of Names column. Since columns are unpivoted, calculations base on Picture 2 in this post are not accurate. The results that I should get are:



So total number of X is 3, divided by total number of names (3), and we get 100%. Total number of Y is 2, divided by total number of names (3), and we get 0.67.

I tried using Quick Mesaure to divide Count of Substances by Count of distinct names, but I don't get accurate results. 

How I can solve this?

Thank you.

[Greg_Deckler]

You probably need a denominator like:

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

[Nejovic]

This works, but there is a problem with filtering. 

So in the denominator I only need to have some values that are filterer by another column. Let me try to explain:

If this is my data:



I only need distinct values in 'Name' column that have 'L' status. 

I run your code and it works:


But when I filter only Names that have 'L' status, tha value of denominator stays the same (3) and I got next result:



But here the value of denominator should be 2.

[Nejovic]

I also want denominator to change when I filter names by other columns. If I calculate it in proposed way, then the value of denominator is fixed. 

[Greg_Deckler]

Couple different things you can do. 

Denominator = 
VAR __table = FILTER(ALL('Table'[Name]),[Status]="L")
VAR __table1 = DISTINCT(__table)
RETURN
COUNTROWS(__table1)

Or:

Denominator = 
VAR __table = SELECTCOLUMNS(ALLEXCEPT('Table',[Status]),"__name",[Name])
VAR __table1 = DISTINCT(__table)
RETURN
COUNTROWS(__table1)

[Nejovic]

Furthermore, 

I tried to set denomintor using next code:

Denominator = 
VAR __table1 = DISTINCTCOUNT(Sheet2[Name])
RETURN
__table1

Now the denominator is responsive to filters, but he Quick Measure (Value/Denominator) doesn't show the correct resuls. Please see the image below. 


Visual 'Counte of Value by Value' is good, just as Denominator. But visual 'Count of Value Divided by Denominator by Value' doesn't show expected results. 

[Nejovic]

I'm still getting incorrect results. 

As I said, I have other columns that I need to filter with, and the value of the denominator do not change. 

Let me augment my table. To make this simpler, only status now is 'L' so, there is no need to filter table by status, but I'll have to filter by 'Age Group' and 'Year'.

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

Once again, the goal should be 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 do not get these results when I apply suggested codes, simply because the value of denominator do not change when I apply filters. 

For example, when I apply 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. 

Please let me know if you need some additional information. 

Thank you!