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

Show As a Percentage of Unique Values in a Column

Hi everyone, 

 

This is the structure of my data:

1.png

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

2.png

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)

3.png

 

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:

4.png

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.

6 REPLIES 6
Super User
Super User

Re: Show As a Percentage of Unique Values in a Column

You probably need a denominator like:

 

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Nejovic Regular Visitor
Regular Visitor

Re: Show As a Percentage of Unique Values in a Column

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:

6.png

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

I run your code and it works:

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

8.png

But here the value of denominator should be 2.


Nejovic Regular Visitor
Regular Visitor

Re: Show As a Percentage of Unique Values in a Column

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. 

Super User
Super User

Re: Show As a Percentage of Unique Values in a Column

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)

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Nejovic Regular Visitor
Regular Visitor

Re: Show As a Percentage of Unique Values in a Column

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

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

 

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. 

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

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. 

Please let me know if you need some additional information. 

Thank you!

 

Nejovic Regular Visitor
Regular Visitor

Re: Show As a Percentage of Unique Values in a Column

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. 

24.png
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.